Sumproduct as a Dynamic VBA code

[Bastbog]

Hi

I am trying to do a sumproduct as a dynamic vba code but I cannot get it to work.
I'd use XL2BB but i'm on a work computer so i can't install anything, so attaching picture for some clarity.

This is for an order form and the reason i want it dynamic is because the number of rows changes depending on what supplier i'm ordering from.

Column B is empty and filled by me, Column C is already filled in and done.
I made a macro to clean up the spreadsheet but this is so far the only thing i cannot get to work, hopefully i'm making sense.

 

[Joe4]

Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:

Sub MyMacro()
    Dim lr As Long
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
'   Put formula in column C after last row
    Range("C" & lr + 1).Formula = "=SUMPRODUCT(B2:B" & lr & "*C2:C" & lr & ")"
End Sub

 

[Bastbog]

Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:

Sub MyMacro()
    Dim lr As Long
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
'   Put formula in column C after last row
    Range("C" & lr + 1).Formula = "=SUMPRODUCT(B2:B" & lr & "*C2:C" & lr & ")"
End Sub

 

[Bastbog]

Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:

Sub MyMacro()
    Dim lr As Long
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
'   Put formula in column C after last row
    Range("C" & lr + 1).Formula = "=SUMPRODUCT(B2:B" & lr & "*C2:C" & lr & ")"
End Sub

Hi and thank you!

It does not input anything in the cell.
i've updated the image to more accurately reflect real world scenario and the code is below.

I know it's not the best written code seen by man, i'm just starting out!

Sub MyMacro()
    Dim lr As Long
'   Find last row in column C with data
    lr = Cells(Rows.Count, "P").End(xlUp).Row
'   Put formula in column P after last row
    Range("P" & lr + 1).Formula = "=SUMPRODUCT(M2:M" & lr & "*P2:P" & lr & ")"
    

' Makro1 Makro
'
' Kortkommando: Ctrl+å
'
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1:AA1").Find("Beställning FP").Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[1]"
    Selection.AutoFill Destination:=Range("N2:N" & Range("A" & Rows.Count).End(xlUp).Row)
    Columns("D:D").EntireColumn.AutoFit
    Range("A1:AA1").Find("Inköpspris (VB)").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Application.CutCopyMode = False
    Range("P" & lr + 1).Formula = "=SUMPRODUCT(M2:B" & lr & "*P2:P" & lr & ")"
    Selection.NumberFormat = "#,##0.00 $"
   
End Sub

 

[Joe4]

What is your other code supposed to be doing?
Perhaps you are calculating last row too early.

 

[Bastbog]

What is your other code supposed to be doing?
Perhaps you are calculating last row too early.

It gathers data from another sheet and tidies it up a bit, it does work but i know it's not the smoothest code.
This is how it ends up after reaching "sumproduct", nothing enters.

 

[Joe4]

Sorry, I was out of town for a few days.

Make sure that you are on the correct page when the code that calculates the "lr" (last row).
You may want to change this row:

lr = Cells(Rows.Count, "P").End(xlUp).Row

to this:

lr = Sheets("SheetName").Cells(Rows.Count, "P").End(xlUp).Row

being sure to replace SheetName with the actual name of your sheet.
This will ensure it is running on the correct sheet.

You should also move that line down, just before this line:

Range("P" & lr + 1).Formula = "=SUMPRODUCT(M2:B" & lr & "*P2:P" & lr & ")"

because you do not want to calculate the last row until right before you are going to use it!
Otherwise, it may not be accurate, if you are adding or removing rows after you calculate it.

Also, replace this whole section:

Range("P92").Select
Selection.NumberFormat = "#,##0.00 $"
Range("D92").Select
ActiveCell.FormatR1C1 = "O Total"

with this:

Range("P" & lr + 1).NumberFormat = "#,##0.00 $"
Range("D" & lr + 1).FormatR1C1 = "O Total"

 

[Bastbog]

Sorry, I was out of town for a few days.

Make sure that you are on the correct page when the code that calculates the "lr" (last row).
You may want to change this row:

lr = Cells(Rows.Count, "P").End(xlUp).Row

to this:

lr = Sheets("SheetName").Cells(Rows.Count, "P").End(xlUp).Row

being sure to replace SheetName with the actual name of your sheet.
This will ensure it is running on the correct sheet.

You should also move that line down, just before this line:

Range("P" & lr + 1).Formula = "=SUMPRODUCT(M2:B" & lr & "*P2:P" & lr & ")"

because you do not want to calculate the last row until right before you are going to use it!
Otherwise, it may not be accurate, if you are adding or removing rows after you calculate it.

Also, replace this whole section:

Range("P92").Select
Selection.NumberFormat = "#,##0.00 $"
Range("D92").Select
ActiveCell.FormatR1C1 = "O Total"

with this:

Range("P" & lr + 1).NumberFormat = "#,##0.00 $"
Range("D" & lr + 1).FormatR1C1 = "O Total"

Hey there!

I managed to get it working with a little tweak, here's how it looks now:

 Dim lr As Long
 lr = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).Row
    Range("P" & lr + 1).Formula = "=SUMPRODUCT(M2:M" & lr & "*P2:P" & lr & ")"

Thanks for all your help, much appreciated!