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

chisercfc

New Member
Joined
Dec 9, 2003
Messages
23
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):

Sample1_Page_1.jpg
Sample1_Page_2.jpg
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
I am not entirely sure of what you are trying to do but see if this is in the ball park.

VBA Code:
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
ABCDE
1H DateHoliday NameMonth of the YearMonth-Table Day
21-JanNew Year's Day17
317-JanMartin Luther King Jr. Day123
42-FebGroundhog Day24
512-FebLincoln's Birthday214
614-FebValentine's Day223
721-FebPresidents' Day223
821-FebWashington's Birthday23
91-MarMardi Gras34
102-MarAsh Wednesday315
11
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=MONTH(A2)
 

chisercfc

New Member
Joined
Dec 9, 2003
Messages
23
I am not entirely sure of what you are trying to do but see if this is in the ball park.

VBA Code:
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
ABCDE
1H DateHoliday NameMonth of the YearMonth-Table Day
21-JanNew Year's Day17
317-JanMartin Luther King Jr. Day123
42-FebGroundhog Day24
512-FebLincoln's Birthday214
614-FebValentine's Day223
721-FebPresidents' Day223
821-FebWashington's Birthday23
91-MarMardi Gras34
102-MarAsh Wednesday315
11
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=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
 

Forum statistics

Threads
1,144,341
Messages
5,723,808
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top