Help with macro?

[bkuu]

slowly learning a bunch of cool formulas and I've created a bunch of templates with Marcos but this particular project got me stumped and I have no idea where to start. I simply have data that I can copy/paste into sheet1 as pictured and want the end result to auto generate sheet2 with Count and Group column auto expanding and merging if there is new data in sheet1 but starting a new group if the timeslot changes. This something difficult to do? I was looking into Vlookup but this seems like a macro thing.

 

[bkuu]

Are you adding it BEFORE or AFTER running the VBA code?

before running the code

 

[Joe4]

before running the code

Then you need to shift all the column references for column C and all the formulas references pertaining to column C over one more column, i.e.

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim r1 As Long
    Dim r2 As Long
    Dim a As Long
    Dim m As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheets
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

'   Copy data from first sheet to second
    ws1.Cells.Copy
    ws2.Select
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Find last row with data on second sheet
    lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Insert columns A and C and add titles
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1") = "Count"
    Range("D1") = "Group"
    
'   Set initial value of row and ASCII code character for group
    r = 2
    a = 65
    
'   Loop through rows
    ws2.Activate
    Do
'       Exit once past row
        If r > lr Then Exit Do
'       Put formula in column A and D
        Cells(r, "A").FormulaR1C1 = "=COUNTIF(C[4],RC[4])"
        Cells(r, "D").Formula = "=CHAR(" & a & ")"
'       Merge cells
        m = Cells(r, "A").Value
        If m > 1 Then
            Range(Cells(r, "A"), Cells(r + m - 1, "A")).Merge
            Range(Cells(r, "D"), Cells(r + m - 1, "D")).Merge
        End If
'       Move to next section and increment group
        r = r + m
        a = a + 1
    Loop
    
'   Center columns A and D
    With Columns("A:A")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Columns("D:D")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[bkuu]

Then you need to shift all the column references for column C and all the formulas references pertaining to column C over one more column, i.e.

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim r1 As Long
    Dim r2 As Long
    Dim a As Long
    Dim m As Long
   
    Application.ScreenUpdating = False
   
'   Set worksheets
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

'   Copy data from first sheet to second
    ws1.Cells.Copy
    ws2.Select
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'   Find last row with data on second sheet
    lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Insert columns A and C and add titles
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1") = "Count"
    Range("D1") = "Group"
   
'   Set initial value of row and ASCII code character for group
    r = 2
    a = 65
   
'   Loop through rows
    ws2.Activate
    Do
'       Exit once past row
        If r > lr Then Exit Do
'       Put formula in column A and D
        Cells(r, "A").FormulaR1C1 = "=COUNTIF(C[4],RC[4])"
        Cells(r, "D").Formula = "=CHAR(" & a & ")"
'       Merge cells
        m = Cells(r, "A").Value
        If m > 1 Then
            Range(Cells(r, "A"), Cells(r + m - 1, "A")).Merge
            Range(Cells(r, "D"), Cells(r + m - 1, "D")).Merge
        End If
'       Move to next section and increment group
        r = r + m
        a = a + 1
    Loop
   
'   Center columns A and D
    With Columns("A:A")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Columns("D:D")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
   
    Application.ScreenUpdating = True
   
End Sub

okay. Makes sense. I made the error of replacing C with D in "=COUNTIF(C[4],RC[4])". Works now. Thanks again for your help

 

[Joe4]

okay. Makes sense. I made the error of replacing C with D in "=COUNTIF(C[4],RC[4])". Works now. Thanks again for your help

Yes, that "C" does not stand for column C. It R1C1 range referencing (where "R" stands for "Row" and "C" stands for "Column").

It is a different way of referencing cells than the "Range" references you are probably used to.
There is a good write-up on it here: Excel Reference Styles
The nice thing about that is you can use relative-range references in that notation, instead of having to hard-code things which may not work well on variable ranges.

 

[bkuu]

Yes, that "C" does not stand for column C. It R1C1 range referencing (where "R" stands for "Row" and "C" stands for "Column").

It is a different way of referencing cells than the "Range" references you are probably used to.
There is a good write-up on it here: Excel Reference Styles
The nice thing about that is you can use relative-range references in that notation, instead of having to hard-code things which may not work well on variable ranges.

gotcha. Thanks for the info

 

[Joe4]

You are welcome!

Note that if you turn on the Macro Recorder, and record yourself entering a formula that has cell references, it almost always uses R1C1 notation by default.
So you can see what that formula looks like. You will also see the phrase .FormulaR1C1 to refer to the formula instead of .Formula.
Just may be interesting to play around with, if you have the time, to see how these things are used...