Help with incremental loop

[IOO]

Hello, I'm hoping someone could help me with my code. I'm trying to autofill/complete blank cells after last used cell in column A until target year value is reached.
Years are entered in col A from another script, what I need to accomplish is if the last year value in col A is less than current year (as of this post current year is 2021), complete the cells down until year value = 2021.

Here is my current code, but it only fills the next empty cell after the last year entered. Fir instance in the attached test sheet, it will only populate 2017 after 2016. What I would like to see is populate the cells after 2016 until the value is = 2021. Please note, the last year entered in col A is dynamic. I would really appreciate the help. Thanks.

Sub test()
Dim LR As Long
Dim counter As Integer, yearGap As Integer
Dim lastCellvalue As Range


lastRow = Worksheets("test").Range("A" & Rows.Count).End(xlUp).Row
Set lastCellvalue = Worksheets("test").Range("A" & lastRow)
yearGap = Year(Now()) - lastCellvalue
    counter = 0
        Do While counter < yearGap
            If lastCellvalue.Value < Year(Now()) Then
                lastCellvalue.Offset(1).Value = lastCellvalue.Value + 1
                counter = counter + 1
            End If
        Loop
End Sub

When I run it, it only populates the next row and exits. Please help. Attached is

 

[Marc L]

Hi, an Excel basics VBA demonstration as a beginner starter :​

Sub Demo1()
    Dim Rg As Range, Y%
    Set Rg = [Test!A1].End(xlDown)
    Y = Year(Date)
    If Rg.Value2 < Y Then Rg.AutoFill Rg.Resize(Y - Rg.Value2 + 1), 2
    Set Rg = Nothing
End Sub

​

As a reminder nothing is 'dynamic' under Excel …​

 

[Zot]

Try

Sub test()
Dim LR As Long
Dim counter As Integer, yearGap As Integer
Dim lastCellvalue As Range


LR = Worksheets("test").Range("A" & Rows.Count).End(xlUp).Row
Set lastCellvalue = Worksheets("test").Range("A" & LR)
yearGap = Year(Now()) - lastCellvalue
counter = 0
Do While counter < yearGap
    counter = counter + 1
    Worksheets("test").Range("A" & LR + counter) = lastCellvalue + counter
Loop

End Sub

 

[IOO]

Try

Sub test()
Dim LR As Long
Dim counter As Integer, yearGap As Integer
Dim lastCellvalue As Range


LR = Worksheets("test").Range("A" & Rows.Count).End(xlUp).Row
Set lastCellvalue = Worksheets("test").Range("A" & LR)
yearGap = Year(Now()) - lastCellvalue
counter = 0
Do While counter < yearGap
    counter = counter + 1
    Worksheets("test").Range("A" & LR + counter) = lastCellvalue + counter
Loop

End Sub

Thank you Zot! Just the tweak I needed, works perfectly. Thanks for the help!

 

[Zot]

Thank you Zot! Just the tweak I needed, works perfectly. Thanks for the help!

Glad to tweak though the code can be simpler like @Marc L