Sumproduct in VBA problems

D

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hello all,

I'm being a bit thick today...

I wish to use a sumproduct formula in VBA. Here is a sample that I've been using on a worksheet:-
Holos Control.xls
ABCDEFG
123BSNETCONSTRUCTIONCONTRACTSINPROG.NetConstructionContractsinProg.0.001YTS
124TSNetInterest-ExternalNetInterest-External0.001YNetInterest-Interco
125TSNetInterest-IntercoNetInterest-Interco0.001YY
126TSNoOracleequivalentAbnormalItems-Gross0.000N
127TSNoOracleequivalentAbnormalP/(L)-95/96Restructuring0.000N1
Oracle to Holos F Mapping


When I use this code in VBA I get a type mismatch error:-

Code: 
Sub TestOfSumProduct()
    Dim rngeA As Range, rngeC As Range, rngeE As Range
    Dim strLine As String, strSheet As String, strFlag As String
    Dim vResult As Variant


    Set rngeA = shtOracleToHolosMap.Range("A1:A265")
    Set rngeC = shtOracleToHolosMap.Range("C1:C265")
    Set rngeE = shtOracleToHolosMap.Range("E1:E265")


    strLine = "Net Interest - Interco"
    strSheet = "TS"
    strFlag = "Y"


    vResult = WorksheetFunction.SumProduct((rngeC.Value = strLine) * _
                                           (rngeA.Value = strSheet) * (rngeE.Value = strFlag))

End Sub

Can anyone spot what I'm doing incorrectly? Can I even use sumproduct in this manner? Thanks for any help :)

Dan
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
You can't use SUMPRODUCT in that manner. SUMPRODUCT uses arrays to perform its calculation and you are trying to use a RANGE object. Can you use something like this instead?

Code: 
    Dim strLine As String, strSheet As String, strFlag As String
    Dim vResult As Variant

    strLine = "Net Interest - Interco"
    strSheet = "TS"
    strFlag = "Y"
    For i = 1 To 265
        vResult = vResult + (Cells(i, 1) = strSheet And _
            Cells(i, 3) = strLine And Cells(i, 5) = strFlag) * -1
    Next i

Best regards,
 
Upvote 0
Hi Dan,

How about something like this:
Code: 
Sub TestOfSumProduct()
    Dim rngeA As Range, rngeC As Range, rngeE As Range
    Dim strLine As String, strSheet As String, strFlag As String
    Dim vResult As Variant


    Set rngeA = shtOracleToHolosMap.Range("A1:A265")
    Set rngeC = shtOracleToHolosMap.Range("C1:C265")
    Set rngeE = shtOracleToHolosMap.Range("E1:E265")


    strLine = "Net Interest - Interco"
    strSheet = "TS"
    strFlag = "Y"


    vResult = Evaluate("SumProduct(((" & rngeC.Address & ") = """ & strLine & _
        """) * " & "((" & rngeA.Address & ") = """ & strSheet & """) * " & _
        "((" & rngeE.Address & ") = """ & strFlag & """))")
    
    MsgBox vResult
    
End Sub
A bit messy, but it seems to work :wink:
 
Upvote 0
Thanks for your replies guys.

Barrie. That sort of makes sense. However, my thinking was that if you do something like this:-

Set rngeA = shtOracleToHolosMap.Range("A1:A265")
vResult = rngeA

Then vResult will be an array. I was hoping that sumproduct would recognise it as such :( The array in this case would be 2 dimensional which might be why I was off track.

<hr>
Richie, ta mate! I ran your code against Barrie's for 1000 iterations and it came in at 1.25 seconds compared to 16.25 for Barrie's (but thanks anyway Bazza!). This is what I'll use just for this month end until I work out a different way of doing it next month.
 
Upvote 0
You can hardcode it as well Dan:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> TestOfSumProduct2()
<SPAN style="color:darkblue">Dim</SPAN> vResult <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>
vResult = _
[SUMPRODUCT(($A$1:$A$265="TS")*($C$1:$C$265="Net Interest - Interco")*($E$1:$E$265="Y"))]
MsgBox vResult
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

It's gets to be long as such, you could slide in sheet references as well...

Yes, I think it's odd that S.P. isn't working as such, it's a 2d array in the W.S. as well...
 
Upvote 0
Thanks! I should have looked myself - under "sumproduct" I assume? Anyway - I'll read it and try it out. Marie
 
Upvote 0
You must log in or register to reply here.

Similar threads

Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
348
Agnarr
Agnarr
thelukeee
  • Question
Importing .Txt files based on date last modified, then sort file by Comma Separation (VBA)---Error: "Run-time error '76: Path not Found"
Replies
8
Views
1K
thelukeee
thelukeee
I
  • Question
SUMPRODUCT in VBA - several tries without success
Replies
7
Views
308
Instantaneo
I
M
  • Question
Evaluate SumProduct in VBA returns #NAME?
Replies
0
Views
740
mlan1604
M
C
  • Question
VBA Variable equal to formula with if() Max() Sumproducts() of Ranges
Replies
0
Views
2K
Chipperzs
C

Forum statistics

Threads
1,214,983
Messages
6,122,598
Members
449,089
Latest member
Motoracer88

Share this page

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