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.
<tbody>
</tbody>
A bit further, rows 90+
<tbody>
</tbody>
Further down, at rows 396+
<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:
This gives an error I'm not sure what I did wrong.
Thanks a lot :D
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