VBA to hard copy a value from a stationary cell to another cell based on a date

[armchairandy]

I have been wracking my brain and cannot see how I can do this:

I have a report updated weekly

Report Date
01/03/19

Value for that week
200

i have a set of weekly dates in columns with the total for each week

ie

"I" "J" "K" "L" "M" "N" and so on.........
01/02/19 08/02/19 15/02/19 22/02/19 01/03/19 08/03/19
20 50 150 180

What I need is I assume some vba code to take the 200 and put it in Column "M" as its the same date. But the code would need to do this each week after I input the value & not over write previous data in the columns. It would I know be simpler just to manually put the value against the column date , but the form has multiple users who are basically very lazy!, and want the whole process automated.


Any help would really be appreciated. I'm not fluent in VBA so please be patient.

Andrew

 

[WaterGypsy]

Try this, which assumes your date is in B2 and the total in C2:

Sub Macro1()
Dim iCol As Integer
Range("m1").Activate
For iCol = 0 To 100
    If ActiveCell.Offset(0, iCol).Value = Range("b2").Value Then
        ActiveCell.Offset(1, iCol).Value = Range("c2").Value
        Exit For
    End If
    If iCol = 100 Then
        MsgBox "Date not found in row 1"
    End If
Next iCol
    
End Sub

 

[armchairandy]

Wow!, thank you so much for the quick response - works perfectly

Regards

Andrew