Generate formulas above header

[alialeola]

Hi guys,

It's me again as I got stuck with the below.
I have a macro that inserts just a header on 5 columns after the LastCol with data and I need it to generate some formulas on row 1, on top of my header, as follows:

My files have different numbers of columns and rows, so the formulas should always start with row 3 and end with the last row with data (in the above example row 5).
The SumProduct should always relate to LastCol -1.

I hope it's not too much to ask, pls pls pls help!!

 

[Joe4]

Will G always be the first column with formulas and will K always be the last column?
If not, how can we identify where the last column should be?
Will there be titles in row 2?

 

[davesexcel]

Try this out.

Sub SumProd()
    Dim sh As Worksheet, LstRw As Long, LstCol As Long, jRng As Range, x, xRng As Range
    Set sh = ActiveSheet
    With sh
        LstRw = .Cells(.Rows.Count, "G").End(xlUp).Row
        LstCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set jRng = .Range(.Cells(3, LstCol - 1), .Cells(LstRw, LstCol - 1))
        For x = 7 To LstCol - 2
            Set xRng = .Range(.Cells(3, x), .Cells(LstRw, x))
            .Cells(1, x) = "=SUMPRODUCT(" & xRng.Address & "," & jRng.Address & ")"
        Next x
       .Cells(1, LstCol - 1) = "=sum(" & jRng.Address & ")"
       .Cells(1, LstCol) = "=sum(" & jRng.Offset(, 1).Address & ")"
    End With
End Sub

 

[alialeola]

Will G always be the first column with formulas and will K always be the last column?
If not, how can we identify where the last column should be?
Will there be titles in row 2?

Thanks for your reply.

My macro identifies the last column, after which it generates 5 columns, only with specific titles.
They are not always on the same column (position), but the titles and the order are always the same (so they will always be the last 6 in a table).

Now I need to add formulas on top of these new columns.
This is my code so far, so you can better understand what I mean.

Sub Master_macro()

    Dim LastRow As Long
    Dim LastCol As Long
    Dim iRow As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    With ws
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column ' needs to be a 2 due to headers start on row 2
        .Columns(LastCol + 1).EntireColumn.Insert
        With .Cells(2, LastCol + 1)
            .Value = "aaa"
            .Interior.Color = 192
            .Font.Color = vbWhite
        End With
        .Columns(LastCol + 2).EntireColumn.Insert
        With .Cells(2, LastCol + 2)
            .Value = "bbb"
            .Interior.Color = 15773696
            .Font.Color = vbWhite
        End With
        .Columns(LastCol + 3).EntireColumn.Insert
        With .Cells(2, LastCol + 3)
            .Value = "ccc"
            .Interior.Color = 5296274
            .Font.Color = vbWhite
        End With
        .Columns(LastCol + 4).EntireColumn.Insert
        With .Cells(2, LastCol + 4)
            .Value = "ddd"
            .Interior.Color = 49407
            .Font.Color = vbWhite
        End With
        .Columns(LastCol + 5).EntireColumn.Insert
        With .Cells(2, LastCol + 5)
            .Value = "fff"
            .Interior.Color = 10498160
            .Font.Color = vbWhite
        End With
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection
        .BorderAround xlContinuous, xlThin, 0
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .Weight = xlThin
        End With
    End With
End With
End Sub

 

[Joe4]

Did you try Davesexcel code to see if it does what you need?

In your example, where you are adding 5 columns (G - K), how exactly is the data in range G3:K5 being updated/entered?

 

[alialeola]

Try this out.

Sub SumProd()
    Dim sh As Worksheet, LstRw As Long, LstCol As Long, jRng As Range, x, xRng As Range
    Set sh = ActiveSheet
    With sh
        LstRw = .Cells(.Rows.Count, "G").End(xlUp).Row
        LstCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Set jRng = .Range(.Cells(3, LstCol - 1), .Cells(LstRw, LstCol - 1))
        For x = 7 To LstCol - 2
            Set xRng = .Range(.Cells(3, x), .Cells(LstRw, x))
            .Cells(1, x) = "=SUMPRODUCT(" & xRng.Address & "," & jRng.Address & ")"
        Next x
       .Cells(1, LstCol - 1) = "=sum(" & jRng.Address & ")"
       .Cells(1, LstCol) = "=sum(" & jRng.Offset(, 1).Address & ")"
    End With
End Sub

Thanks so much for your reply.
The code is generating formulas as follows:

It would be great if it could start with row 3, below my header.
Either way, it's helping as I can manually adjust the formulas afterward.

 

[davesexcel]

Not sure why the formula is starting at 2? the code shows the range in the formula starting at row 3

Set xRng = .Range(.Cells(3, x), .Cells(LstRw, x))

note:
oh, I see
You sample image has no data. The code is using column G as the column to count rows, can you change it to column A?

 LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row

 

[alialeola]

Not sure why the formula is starting at 2? the code shows the range in the formula starting at row 3

Set xRng = .Range(.Cells(3, x), .Cells(LstRw, x))

note:
oh, I see
You sample image has no data. The code is using column G as the column to count rows, can you change it to column A?

 LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row

It's working perfectly now, exactly what I needed.

Thank you so so much, greatly appreciated!