SOLVED
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.
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.
@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)
