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.

 

[varan]

Hi RickXL
It works perfectly now. Really appreciate your help. Once again thanks a lot!

 

[RickXL]

No problem.

Thanks for letting me know.

Regards,

 

[varan]

Hi rick
I\ve to further develop this code, that is to give the user the option of choosing the workbook and then running the code you assisted me with in the beginning. As of now the code stands this way-
Private Sub CommandButton1_Click()
Dim fNameAndPath As Variant
Dim wb As Workbook
Dim wbb As Workbook


Dim ary1 As Variant
Dim ary2 As Variant
Dim sum As Double
Dim i As Long


fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Set wb = Workbooks.Open(fNameAndPath)


With wb
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)
sum1 = WorksheetFunction.sum(ary2)
End If
Next
.Range("G22") = sum / sum1
End With
End Sub

The code works to the extent where the user is prompted to open the file from the folder but it doesnt load for the next part of the code.
This is the error that occurs
Expected Function or variable

at 'with wb' line of the code. Is there a way to only get the file name chosen by the user and then trasferring it to the next lines in the code?

 

[RickXL]

One thing you have missed out is the Worksheet. The hierarchy is:
Workbook-->Worksheet-->Range
As written, when your macro runs it does not know which Worksheet in the Workbook it needs to be looking at.

    With wb
        ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

is a short way or writing:

        ary1 = [B][COLOR=#ff0000]wb[/COLOR][/B].Range("A2", [B][COLOR=#ff0000]wb[/COLOR][/B].Cells([B][COLOR=#ff0000]wb[/COLOR][/B].Rows.Count, "A").End(xlUp))

I am not sure how you want to enter the worksheet name. ActiveSheet might be an option, as in:

    With ActiveSheet
        ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

or perhaps you know it will always be the first Worksheet in the Workbook. In which case you might use:

    With wb.worksheets(1)
        ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

On a side issue, I am not sure why you are calculating sum1 that way. You are doing the same sum calculation for every row of the data and getting the same answer each time.


Regards,

 

[varan]

Hi rick,
activesheet seems to be the best option. I calculate that as I need to divide the "sum" with total of the value in column D and thus made a new term sum1.

 

[varan]

And now I realise its wrong. Changed the code

 

[varan]

Hi rick,
I want the sum of numbers in Column D of the range present in Column A * Column D as in your previous code. I'm not able to get that as it is suming up all the numbers that are there in column D as column D has extra numbers in it.

 

[RickXL]

You mean like this:

Private Sub CommandButton1_Click()
    Dim fNameAndPath  As Variant
    Dim ary1          As Variant
    Dim ary2          As Variant
    Dim sum           As Double
    Dim sum1          As Double
    Dim i             As Long
    
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Workbooks.Open (fNameAndPath)
    
    With ActiveSheet
        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)
                sum1 = sum1 + ary2(i, 1)
            End If
        Next
        .Range("G22") = sum / sum1
    End With
End Sub

 

[varan]

Hi rick, I did that and it did not work. There ia a value in D10 that was getting added into the sum and that was not supposed to happen. I instead changed the value to "D10" in ary2 ststement.

Thank you for the response.