SOLVED

Dynamic EOMONTH VBA code

Frequent Contributor

How do i amend the code below in way which it could display the months in column A using the EOMONTH formula? Kind of got stuck on how to input my i in the for loop

 

The placement of the highlighted portions in yellow are dynamic.

Capture.JPG

 

 

Dim sum5 As Long
Dim period As Range
Dim ws As Worksheet
Dim startdate As Range
Dim contractterm As Range
Dim i As Long
Dim period1 As Range

Set ws = Sheet1
sum5 = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

Set period = ws.Range("A" & sum5 + 2)
period.Value = "Period"

period.Offset(0, 1).Value = "Receipts"
period.Offset(0, 2).Value = "Payments"
period.Offset(0, 3).Value = "Balance"
period.Offset(0, 4).Value = "Remarks"

With ws.Cells

Set startdate = .Find(What:="Contract Start/Billing date", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
        
If Not startdate Is Nothing Then
startdate.Offset(0, 1).Copy
period.Offset(1).PasteSpecial Paste:=xlPasteValues
period.Offset(1).NumberFormat = "Mmm-yy"


Else

End If

End With

With ws.Cells

Set contractterm = .Find(What:="Contact Term (months)", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

Set period1 = period.Offset(2)

If Not contractterm Is Nothing Then

For i = 1 To contractterm.Offset(0, 1)

period1.Value = Application.EoMonth(period.Offset(1).Value, i - 1) '<----- how should i place my i before the .Value?

Next i

Else
End If

End With

 

 

appreciate the help in advance.

2 Replies
best response confirmed by hrh_dash (Frequent Contributor)
Solution

@hrh_dash Now, I'm far from an expert in VBA but I believe you need to move the Set period1 step into the For i loop and change it to:

 

Set period1 = period.Offset(i)

Riny_van_Eekelen_0-1658047046672.png

 

thank you for the assistance!