Excel-VBA writing a script to calculate the sumproduct

[varan]

Hello,
I'm trying to write script wherein I need to find the sumproduct of numbers in column A and column D. The issue is that this data length varies from sheet to sheet hence I need to write a script that finds the number of rows that has a number present in it and then compute the sumproduct for this data. Need help as I'm using VB for the very first time.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

It may be easier just to use a Table, formerly known as a ListObject. Tables will cope with the addition of rows.

To turn your data into a Table, just select a cell within the data then hit Ctrl + T. You will be asked if the range is OK and whether there are headings or not.

Then you can enter your SUMPRODUCT formula. Make sure that it is not entered in a cell touching the Table or Excel will add a row or column to your Table.

Use Excel to help you complete the SUMPRODUCT formula by selecting the ranges with the mouse. I had a Table called Table1 with column names of X1 and X4 for columns A and D. My formula looked like this: =SUMPRODUCT(Table1[X1],Table1[X4])

Now I can drag the whole Table to a different place on the Worksheet, or rename the columns, or add a column or some rows and the formula will still work.

Using Dynamic Named Ranges is another option. If you still want a VBA solution, come back, and I will show you how.


Regards,

 

[varan]

Hi RickXL,
Your suggestions worked and thank you for the assistance. There is actually a requirement of a template to be made where the script runs and computes the sumproduct for all the sheets and these values should appear in master sheet. Hence need to do it with VBA

Regards

 

[RickXL]

OK, here is a straight VBA version:

Sub SumProd()
    Dim ary1  As Variant
    Dim ary2  As Variant
    Dim sum   As Double
    Dim i     As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        ary2 = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
        For i = 1 To UBound(ary1)
            sum = sum + ary1(i, 1) * ary2(i, 1)
        Next
        .Range("G1") = sum
    End With
End Sub

It reads the first column, A, into ary1 and the second column, D, into ary2.
Then it multiplies the numbers together row by row summing the result in a variable called: sum.

I am not sure what you want to do with the output but I wrote it to cell G1.
I assumed Sheet1, as well.


Regards,

 

[varan]

[h=1]Hi,
I'm getting this error
Subscript out of range (Error 9)[/h]

 

[RickXL]

What is your Worksheet name and do you have the same number of items in both columns A and D?

Which line the error occurs on would help, too.


Regards,

 

[varan]

Hi that got solved and now its this error [h=1]Type mismatch (Error 13), Office Shared [vblr6.chm1000013][/h]occuring at this line-
sum = sum + ary1(i, 1) * ary2(i, 1)

 

[RickXL]

I did wonder if I should force those numbers to be the same but then I reasoned that if you had a different number of numbers in each column then it was something that needed sorting out rather than hiding. So I left it as it was.

 

[varan]

Hi, I just found out the issue. in some of the lines there is an alphabet present and thus the error is popping up. Is there a way to skip these rows in computation? I found a command wherein these can be deleted and run but since the original file gets modified it isnt the preferred way of doing it.

regards,

 

[RickXL]

This version checks to see if both array elements are numeric first. If they both are then it multiplies them together and sums them.

Sub SumProd()
    Dim ary1  As Variant
    Dim ary2  As Variant
    Dim sum   As Double
    Dim i     As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
        ary2 = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
        For i = 1 To UBound(ary1)
            If IsNumeric(ary1(i, 1)) And IsNumeric(ary2(i, 1)) Then
                sum = sum + ary1(i, 1) * ary2(i, 1)
            End If
        Next
        .Range("G1") = sum
    End With
End Sub

Regards,