VBA Offset challenge/problem

[ebea]

Hi! I run into a problem, with the Offset function, and how to implement it in my code.
In Column H1:FT1 I have month's placed.
What I need to do, is to place the date in F2 in the correct month in the range H2:FT2 (marked in Yellow), when I run my code, from push on Button "Create M.". As now, when I run my code, It place the date in H2.
I have tried many ways around, to get the Offset function, placed correctly in my code, so it ends up in the correct Column, but so far, without any luck.

Hopefully someone have the answer

The code are:

Option Explicit
Sub months()
Dim x As Long
Dim ws As Worksheet
Set ws = Worksheets("Ark1")

ws.Range("H2") = WorksheetFunction.EDate(ws.Range("F2") + 0, 0)
Range("H2").AutoFill Destination:=Range(Cells(2, 8), Cells(2, 8 + Range("G2").Value))
End Sub

Customer	Cost		Sales		Start date	Months period	jan-24	feb-24	mar-24	apr-24	maj-24	jun-24	jul-24	aug-24	sep-24	okt-24	nov-24	dec-24	jan-25	feb-25	mar-25	apr-25
A	379,00		399,00		01-03-2024	24	mar-24	apr-24	maj-24	jun-24	jul-24	aug-24	sep-24	okt-24	nov-24	dec-24	jan-25	feb-25	mar-25	apr-25	maj-25	jun-25
							20	20	20	20	20	20	20	20	20	20	20	20	20	20	20	20
B	729,00		759,00		01-02-2024	36
C	449,00		459,00		01-03-2024
D	569,00		599,00		01-04-2024
E					01-05-2024
						Total

Regards

 

[rollis13]

Not sure if I correctly understood your goal but try these changes:

Option Explicit
Sub months_new()
    Dim ws    As Worksheet
    Dim diff  As Integer
    Set ws = Worksheets("Ark1")
    With ws
        diff = DateDiff("m", .Range("C1"), .Range("F2")) '<- calculates offset by month (please check if I got the right cells)
        .Cells(2, 8 + diff) = WorksheetFunction.EDate(.Range("F2") + 0, 0)
        .Cells(2, 8 + diff).AutoFill Destination:=.Range(Cells(2, 8 + diff), Cells(2, 8 + diff + .Range("G2").Value))
    End With
End Sub

 

[ebea]

Thank you, rollis13. It was Spot On.
I was around, something similar, but surely missed something

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.