Automated Archiving

T

tom300181

I have a sheet that has a variable cell (date b1) and an entry for Sale
(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 t
A7-AE7 under the date from B1.

Can this be done?

Thanks

To
 
T

tom300181

I have a macro that looks like this........ It doesn't currently wor
(I am very poor on VB!) this is the code up to 3rd day of the month.
Obviously I would have to duplicate this for the rest of the dat
range. Can anybody help me out with how to get this to work
?
Thanks

Tom


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

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

Sub one()

If ("B1") = "1" Then

Range("A7").Select
ActiveSheet.Paste

Else: two


End If
End Sub



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

Range("B7").Select
ActiveSheet.Paste

Else: three


End If
End Sub

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

Range("C7").Select
ActiveSheet.Paste

Else: End


End If
End Su
 
D

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
 
Top