Automated Archiving

[tom300181]

I have a sheet that has a variable cell (date b1) and an entry for Sales (B3). I also have A6 -> AE6 with numbers 1-31,

I want a formula that will look at B1 and add the contents of B3 to to A7-AE7 under the date from B1.

Can this be done?

Thanks

Tom

[tom300181]

I know have a working macro...........


**************************************************
Sub test()
'
' test Macro
' Macro recorded 01/08/2005 by bailete
'

'
Range("B3:B4").Select
Selection.Copy
one
End Sub

Sub one()

If Range("B1") = 1 Then

Range("A7").Select
ActiveSheet.Paste

Else: two


End If
End Sub



Sub two()
If Range("B1") = 2 Then

Range("B7").Select
ActiveSheet.Paste

Else: three


End If
End Sub

Sub three()
If Range("B1") = 3 Then

Range("C7").Select
ActiveSheet.Paste

Else: four


End If
End Sub
Sub four()
If Range("B1") = 4 Then

Range("D7").Select
ActiveSheet.Paste

Else: five


End If
End Sub

Sub five()
If Range("B1") = 5 Then

Range("E7").Select
ActiveSheet.Paste

Else: six


End If
End Sub

Sub six()

If Range("B1") = 6 Then

Range("F7").Select
ActiveSheet.Paste

Else: seven


End If
End Sub



Sub seven()
If Range("B1") = 7 Then

Range("G7").Select
ActiveSheet.Paste

Else:


End If
End Sub

Sub eight()
If Range("B1") = 8 Then

Range("H7").Select
ActiveSheet.Paste

Else: nine


End If
End Sub
Sub nine()
If Range("B1") = 9 Then

Range("I7").Select
ActiveSheet.Paste

Else: ten


End If
End Sub

Sub ten()
If Range("B1") = 10 Then

Range("J7").Select
ActiveSheet.Paste

Else: eleven


End If
End Sub
Sub eleven()
If Range("B1") = 11 Then

Range("K7").Select
ActiveSheet.Paste

Else: twelve


End If
End Sub
**************************************************

My problem is that this is getting very long and I am sure that an easier way to do thisw must exist.

Can anyone help?

Thanks

Tom

[tom300181]

Can anybody be of assistance????

Thanks

Tom

[Dave Peterson]

Does B1 contain a real date or does it contain a number from 1 to 31?

Maybe something like this will get you started.

Option Explicit
Sub testme01()


Dim DestCell As Range
Dim myValue As Double
Dim DayValue As Variant
Dim myMsg As String

With Worksheets("sheet1")
Set DestCell = .Range("a6")
myValue = .Range("B3").Value
DayValue = .Range("B1").Value

' or
' If IsDate(DayValue) Then
' DayValue = Day(DayValue)
' End If

myMsg = "Invalid Value"
If IsNumeric(DayValue) Then
DayValue = CLng(DayValue)
If DayValue < 1 _
Or DayValue > 31 Then
'do nothing
Else
myMsg = ""
End If
End If

If myMsg <> "" Then
MsgBox myMsg
Exit Sub
End If

DestCell.Offset(1, DayValue - 1).Value = myValue
End With

End Sub









tom300181 wrote:
>
> I have a sheet that has a variable cell (date b1) and an entry for Sales
> (B3). I also have A6 -> AE6 with numbers 1-31,
>
> I want a formula that will look at B1 and add the contents of B3 to to
> A7-AE7 under the date from B1.
>
> Can this be done?
>
> Thanks
>
> Tom
>
> --
> tom300181
> ------------------------------------------------------------------------
> tom300181's Profile: http://www.excelforum.com/member.php...o&userid=21580
> View this thread: http://www.excelforum.com/showthread...hreadid=391750

--

Dave Peterson