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 |
