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

Amesthyite

New Member
Joined
Nov 14, 2017
Messages
2
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.
A
B
C
D
E
1
Year
Month
Day
Day Name
Date
2
1
1
1
Monday
22/09/1980
3
1
1
2
Tuesday
23/09/1980
4
1
1
3
Wednesday
24/09/1980

<tbody>
</tbody>






A bit further, rows 90+

A
B
C
D
E
Year
Month
Day
Day Name
Date
90
1
2
5
Wednesday
19/11/1980
91
1
2
6
Thursday
20/11/1980
92
1
2
7
Friday
21/11/1980

<tbody>
</tbody>






Further down, at rows 396+

A
B
C
D
E
396
2
4
8
Wednesday
21/10/1981
397
2
4
9
Thursday
22/10/1981
398
2
5
1
Friday
23/10/1981
399
2
5
2
Saturday
24/10/1981

<tbody>
</tbody>









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:

Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 14, 2017
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,029
Messages
5,466,116
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top