Joaquin Alonso
New Member
- Joined
- Aug 21, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi, my name is Joaquin Alonso.
I retired 18 years ago and I’m starting to use Excel macros again.
As a test I’m changing a calendar maker code that I copied from an Excel help site. The original calendar provides for a single entry on a specific day. As a first attempt, I was able to successfully add 12 rows to each day of the calendar and expand the 7 original columns to 14.
Well, I’m having trouble when indexing this new array, I:
Problem:
Problem # 1: The indexing that propagates the number of days in the right columns is off.
Problem # 2: The generation of the number of days is incorrect.
----------------------------------------0-----------------------------------------------------
Working with the debug function I was able to isolate the code that is causing Problem # 1 (see above).
However; I need your help as to how to correct the problem. I’ve updated the macro with the following comment at the place where the error is happening:
Thanks for your help.
Code:
I retired 18 years ago and I’m starting to use Excel macros again.
As a test I’m changing a calendar maker code that I copied from an Excel help site. The original calendar provides for a single entry on a specific day. As a first attempt, I was able to successfully add 12 rows to each day of the calendar and expand the 7 original columns to 14.
Well, I’m having trouble when indexing this new array, I:
Problem:
Problem # 1: The indexing that propagates the number of days in the right columns is off.
Problem # 2: The generation of the number of days is incorrect.
----------------------------------------0-----------------------------------------------------
Working with the debug function I was able to isolate the code that is causing Problem # 1 (see above).
However; I need your help as to how to correct the problem. I’ve updated the macro with the following comment at the place where the error is happening:
Thanks for your help.
Code:
' Loop through range A3:N15 incrementing each cell after the "1" |
' cell. |
' ************************************************************ |
' *** One of the problems is in this Statement: |
' *** |
' *** For Each cell In .Range("A3: N15").Select |
' *** |
' *** Its indescriminally propagating the number of days across the top of the |
' *** group of cells for all weeks of the month. Instead of |
' *** selecting the specific cell where to place the number for that day, |
' *** The following cells are the candidates to be propagated with the number of the day: |
' *** |
' *** Cells on group 1 ("A3,C3,E3,G3,I3,K3,M3")_ |
' *** Cells on group 2 ("A16,C16,E16,G16,I16,K16,M16")_ |
' *** Cells on group 3 ("A29,C29,E29,G29,I29,K29,M29")_ |
' *** Cells on group 4 ("A42,C42,E42,G42,I42,K42,M42")_ |
' *** Cells on group 5 ("A55,C55,E55,G55,I55,K55,M55")_ |
' *** Cells on group 6 ("A68,C68,E68,G68,I68,K68,M68") |
' *** |
' ************************************************************ |
For Each cell In .Range("A3:N15") |
RowCell = cell.Row |
ColCell = cell.Column |
' Do if "1" is in first column. |
If cell.Column = 1 And cell.Row = 3 Then |
' Do if current cell is not in 1st column. |
ElseIf cell.Column <> 1 Then |
If cell.Offset(0, -1).Value >= 1 Then |
cell.Value = cell.Offset(0, -1).Value + 1 |
' Stop when the last day of the month has been |
' entered. |
If cell.Value > (FinalDay - StartDay) Then |
cell.Value = "" |
' Exit loop when calendar has correct number of |
' days shown. |
Exit For |
End If |
End If |
' Do only if current cell is not in Row 3 and is in Column 1. |
ElseIf cell.Row > 3 And cell.Column = 1 Then |
cell.Value = cell.Offset(-1, 6).Value + 1 |
' Stop when the last day of the month has been entered. |
If cell.Value > (FinalDay - StartDay) Then |
cell.Value = "" |
' Exit loop when calendar has correct number of days |
' shown. |
Exit For |
End If |
End If |
Next |