New to VBA - create a loop numbered 1 to 9 based on criteria

[Amesthyite]

Hello everyone,

I'm new to VBA coding and trying to create a loop to number years, months and days 1 to 9 based on a specific start date.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Year
[/TD]
[TD]Month
[/TD]
[TD]Day
[/TD]
[TD]Day Name
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Monday
[/TD]
[TD]22/09/1980
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]Tuesday
[/TD]
[TD]23/09/1980
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]Wednesday
[/TD]
[TD]24/09/1980
[/TD]
[/TR]
</tbody>[/TABLE]






A bit further, rows 90+

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Year
[/TD]
[TD]Month
[/TD]
[TD]Day
[/TD]
[TD]Day Name
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]90
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]Wednesday
[/TD]
[TD]19/11/1980
[/TD]
[/TR]
[TR]
[TD]91
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]Thursday
[/TD]
[TD]20/11/1980
[/TD]
[/TR]
[TR]
[TD]92
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]Friday
[/TD]
[TD]21/11/1980
[/TD]
[/TR]
</tbody>[/TABLE]






Further down, at rows 396+

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]396
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]8
[/TD]
[TD]Wednesday
[/TD]
[TD]21/10/1981
[/TD]
[/TR]
[TR]
[TD]397
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]9
[/TD]
[TD]Thursday
[/TD]
[TD]22/10/1981
[/TD]
[/TR]
[TR]
[TD]398
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]Friday
[/TD]
[TD]23/10/1981
[/TD]
[/TR]
[TR]
[TD]399
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]Saturday
[/TD]
[TD]24/10/1981
[/TD]
[/TR]
</tbody>[/TABLE]









So, each one in it's own 1-9 sequence, each column continuing the numbering based on the next criteria - for year I wanted to use 22/09 as starting point (on each 23/09 the year changes +1 within range 1-9) for the month search for 22 and on 23 the month changes +1 within range 1 to 9.

I tried like this:

Sub Macro1()
'
' Macro1 Macro
'
Dim LastRow As Long
For i = 1 To 9
        Cells.Find(What:="22", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
       With LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
        Range("B" & Rows.Count).End(xlUp).Offset(1).Value = .Offset(-1).Value + 1
        .Offset(, 1).Value = i
       End With
Next
               
End Sub

This gives an error I'm not sure what I did wrong.

Thanks a lot :D

 

[My Aswer Is This]

I do not understand what you want.

Please show us a before and after image of what you want.
And I do not understand:
"I'm new to VBA coding and trying to create a loop to number years, months and days 1 to 9 based on a specific start date."

How are we to know what this specific start date is.

 

[Amesthyite]

So, each one in it's own 1-9 sequence, each column continuing the numbering based on the next criteria - for year I wanted to use 22/09 as starting point (on each 23/09 the year changes +1 within range 1-9) for the month search for 22 and on 23 the month changes +1 within range 1 to 9.