Automated Archiving
 

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

I have a macro that looks like this........ It doesn't currently work
(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 date
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 Sub


--
tom300181
------------------------------------------------------------------------
tom300181's Profile: http://www.excelforum.com/member.php...o&userid=21580
View this thread: http://www.excelforum.com/showthread...hreadid=391750

Can anybody be of assistance????

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

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