Simple (I thought) loop, compare, copy and paste question from a somewhat VBA newbie.....

[chisercfc]

On Sheet1, starting in cell A1, I have a column of Holiday dates for a given year (see below). In B1, the corresponding Holiday name. In C1, the numeric month of the year that the Holiday occurs in (pulled from A1). Finally, D1 contains my Month-Table date (see paragraph 2 for an explanation of what the Month-Table data is). Example - Groundhog day for 2022; A4 "2-Feb", B4 "Groundhog Day", C4 "2", D4 "4".
There are 50+entries in each column (the holidays I'm using). This can change year to year. On Sheet2, I have 12 rows, cells 1 thru 37, designated as cells to place the holiday names into for each month of the year. A1 -AK1 for January. B1-BK1 for February. C1-CK1 for March, etc.

Explanation of Month-Tables - The purpose for this is based on building month/day table-data to be used in an InDesign graphic (the tables are NOT BUILT IN EXCEL; the data is simply imported into ID from Excel). Each month table in InDesign (12 for the year) will have 6 rows (weeks) of 7 days (labeled Sunday to Saturday) to allow for all potential possibilities for a Holiday placement in any month in a year. 42 cells (6x7) allows for every placement possibility, although cell 37 is always the last used for any month that has 31 days and starts on a Saturday. In a "Day 1 on Saturday situation for a 31 day month", day 1 is located in row 1, at column 7 and day 31 would be row 6, column 2. So day 1 is actually day 7 in my 42-day table (counting L to R, T to B). Day 31 would be Day 37 in the 42-day table (row 6, column 2). This is only important if you want to understand why New Years Day in 2022 is Month 1, Day 7 to me (it's on a Saturday, so row 1, column 7) and not Calendar Day 1. The Month-Table value for each month's "Day 1" depends on what day of the calendar week it falls on. If it falls on Sunday, it's Day1 in my Month-Table. Wednesday is Day4, Saturday is Day7, etc. The rest of the day's Month-Table dates flow from there. So, Jan 3 of 2022 would be Day 10 in my Month-Table (since Jan 1 is Day 7).

Back to Excel - All I'm trying to do is start at row 1 on Sheet1, and compare each Holiday data-row (A1 to D1) to a month-of-the-year designator (arbitrary; 1-12), and write each month's holidays to their own row. If C1 matches the month designator, then write/paste all the Holiday names (B1) for that month to row "X" on Sheet2. However, they need to go into the proper Month-Table day slot, not their calendar day slot. So 01/01/2022, New Years Day, 2022, would go in G1, not A1 (because New Years Day 2022 is on the first Saturday of Month-Table 1, which is the 7th column of row 1, which is day 7 to me). By extension, row 1 (A1-AK1) would contain all the holiday names in January in their proper Month-Table cell designator (day/cell 1 - 37). Row 2 would contain February's data, row 3 March's data and so on. Obviously, I end up with 12 rows of 37 cells. Per my sample data below, I've already figured out the Month-Table cell identity for all the holidays. Column D. I do all that math in the formulas supporting column D. I just need to get the Holiday Name onto the right row and into that named cell "number" on Sheet2.

All the needed data to paste is on sheet 1; the Holiday name, what row to put it on and what "cell" to paste it in on that row. This really isn't rocket science, but I'm having trouble with the VBA (since I want to add a button). For the first row/value, I'm just trying to say, "Paste the value on Sheet1, cell B2, into cell G1 on Sheet2 if C1 equals the month designator" (loop counter starting with 1 or whatever) - New Year's Day into G1, the 7th cell in row 1, which is Month 1 (January) on Sheet2. Sounds pretty simple.

Needless to say, I'm new to VBA. I'm OK with basic sheets and formulas. I just don't do much with macros/VBA. I was trying to use a button/macro to add these values to an existing sheet, which already has other calendar data on it. I've tried loops, If Then & For Next, A1 vs R1C1, Offset, etc., things that I've seen used in tutorials that I'm not having much luck with. The semantics are throwing me, and I'm not even close to naming and dim'ing my variables properly. So, it's time to turn to the experts. Thanks in advance for any help.

A cut & paste sample of what I'm trying to do, as if I'd processed the first month on sheet 1 (Jan, 2 entries) and posted the data to the first row on sheet 2 (the yellow doesn't mean anything):

 

[Alex Blakenburg]

I am not entirely sure of what you are trying to do but see if this is in the ball park.

Sub CalendarEvents()

    Dim wb As Workbook
    Dim shSrc As Worksheet
    Dim shDest As Worksheet
    Dim rngSrc As Range
    Dim arrSrc As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim rCell As Range
    
    Application.ScreenUpdating = False
    
    Set wb = ThisWorkbook
    Set shSrc = wb.Worksheets("Sheet1")
    Set shDest = wb.Worksheets("Sheet2")
    
    With shSrc
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rngSrc = .Range(.Cells(2, "A"), .Cells(lastRow, "D"))
    End With
    arrSrc = rngSrc.Value2
    
    For i = LBound(arrSrc) To UBound(arrSrc)
        With shDest
            ' if duplicate cell reference concatenate values
            If .Cells(arrSrc(i, 3), arrSrc(i, 4)) <> "" Then
                .Cells(arrSrc(i, 3), arrSrc(i, 4)) = _
                    .Cells(arrSrc(i, 3), arrSrc(i, 4)) & " / " & arrSrc(i, 2)
            Else
                .Cells(arrSrc(i, 3), arrSrc(i, 4)) = arrSrc(i, 2)
            End If
        End With
    
    Next i
    
    shDest.UsedRange.EntireColumn.AutoFit
    
    Application.ScreenUpdating = True

End Sub

And in case anyone else wants to have a go here is my sample data.

20210902 VBA Calendar Events.xlsm
	A	B	C	D	E
1	H Date	Holiday Name	Month of the Year	Month-Table Day
2	1-Jan	New Year's Day	1	7
3	17-Jan	Martin Luther King Jr. Day	1	23
4	2-Feb	Groundhog Day	2	4
5	12-Feb	Lincoln's Birthday	2	14
6	14-Feb	Valentine's Day	2	23
7	21-Feb	Presidents' Day	2	23
8	21-Feb	Washington's Birthday	2	3
9	1-Mar	Mardi Gras	3	4
10	2-Mar	Ash Wednesday	3	15
11
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=MONTH(A2)

 

[chisercfc]

I am not entirely sure of what you are trying to do but see if this is in the ball park.

Sub CalendarEvents()

    Dim wb As Workbook
    Dim shSrc As Worksheet
    Dim shDest As Worksheet
    Dim rngSrc As Range
    Dim arrSrc As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim rCell As Range
   
    Application.ScreenUpdating = False
   
    Set wb = ThisWorkbook
    Set shSrc = wb.Worksheets("Sheet1")
    Set shDest = wb.Worksheets("Sheet2")
   
    With shSrc
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rngSrc = .Range(.Cells(2, "A"), .Cells(lastRow, "D"))
    End With
    arrSrc = rngSrc.Value2
   
    For i = LBound(arrSrc) To UBound(arrSrc)
        With shDest
            ' if duplicate cell reference concatenate values
            If .Cells(arrSrc(i, 3), arrSrc(i, 4)) <> "" Then
                .Cells(arrSrc(i, 3), arrSrc(i, 4)) = _
                    .Cells(arrSrc(i, 3), arrSrc(i, 4)) & " / " & arrSrc(i, 2)
            Else
                .Cells(arrSrc(i, 3), arrSrc(i, 4)) = arrSrc(i, 2)
            End If
        End With
   
    Next i
   
    shDest.UsedRange.EntireColumn.AutoFit
   
    Application.ScreenUpdating = True

End Sub

And in case anyone else wants to have a go here is my sample data.

20210902 VBA Calendar Events.xlsm
	A	B	C	D	E
1	H Date	Holiday Name	Month of the Year	Month-Table Day
2	1-Jan	New Year's Day	1	7
3	17-Jan	Martin Luther King Jr. Day	1	23
4	2-Feb	Groundhog Day	2	4
5	12-Feb	Lincoln's Birthday	2	14
6	14-Feb	Valentine's Day	2	23
7	21-Feb	Presidents' Day	2	23
8	21-Feb	Washington's Birthday	2	3
9	1-Mar	Mardi Gras	3	4
10	2-Mar	Ash Wednesday	3	15
11
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=MONTH(A2)

Alex,

Thank you. I appreciate you taking a look at it. Let me go thru what you've done so I can understand it. I'll get back to you.

CH