Sumproduct as a Dynamic VBA code

Bastbog

New Member
Joined
May 15, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Skärmbild 2024-05-15 143437.png
    Skärmbild 2024-05-15 143437.png
    3.4 KB · Views: 31

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:
VBA Code:
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
 
Upvote 0
Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:
VBA Code:
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
 
Upvote 0
Welcome to the Board!

Assuming that the first row of data starts on line 2, I think this will do what you want:
VBA Code:
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!

VBA Code:
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
 

Attachments

  • Skärmbild 2024-05-15 160138.png
    Skärmbild 2024-05-15 160138.png
    7.7 KB · Views: 21
Upvote 0
What is your other code supposed to be doing?
Perhaps you are calculating last row too early.
 
Upvote 0
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.
 

Attachments

  • skärmbild 2024-05-16.png
    skärmbild 2024-05-16.png
    58.4 KB · Views: 22
Upvote 0
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:
Rich (BB code):
lr = Cells(Rows.Count, "P").End(xlUp).Row
to this:
Rich (BB code):
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:
VBA Code:
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:
VBA Code:
Range("P92").Select
Selection.NumberFormat = "#,##0.00 $"
Range("D92").Select
ActiveCell.FormatR1C1 = "O Total"
with this:
VBA Code:
Range("P" & lr + 1).NumberFormat = "#,##0.00 $"
Range("D" & lr + 1).FormatR1C1 = "O Total"
 
Upvote 1
Solution
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:
Rich (BB code):
lr = Cells(Rows.Count, "P").End(xlUp).Row
to this:
Rich (BB code):
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:
VBA Code:
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:
VBA Code:
Range("P92").Select
Selection.NumberFormat = "#,##0.00 $"
Range("D92").Select
ActiveCell.FormatR1C1 = "O Total"
with this:
VBA Code:
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:
VBA Code:
 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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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
Back
Top