Sumproduct in VBA problems

dk · Nov 4, 2003

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

A

B

C

D

E

F

G

123

BS

NETCONSTRUCTIONCONTRACTSINPROG.

NetConstructionContractsinProg.

0.001

Y

TS

124

TS

NetInterest-External

0.001

Y

NetInterest-Interco

125

TS

NetInterest-Interco

0.001

Y

126

TS

NoOracleequivalent

AbnormalItems-Gross

0.000

N

127

TS

NoOracleequivalent

AbnormalP/(L)-95/96Restructuring

0.000

N

1

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

just_jon · Nov 4, 2003

When you step thru it, what happens at vResult = ?

Barrie Davidson · Nov 4, 2003

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,

Richie(UK) · Nov 4, 2003

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:

dk · Nov 4, 2003

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.

NateO · Nov 4, 2003

You can hardcode it as well Dan:

Sub TestOfSumProduct2()
Dim vResult As Variant
vResult = _
[SUMPRODUCT(($A$1:$A$265="TS")*($C$1:$C$265="Net Interest - Interco")*($E$1:$E$265="Y"))]
MsgBox vResult
End Sub

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...

Amileaux · Jan 5, 2004

Thanks! I should have looked myself - under "sumproduct" I assume? Anyway - I'll read it and try it out. Marie

Sumproduct in VBA problems

dk

MrExcel MVP

Excel Facts

just_jon

Legend

Barrie Davidson

MrExcel MVP

Richie(UK)

MrExcel MVP

dk

MrExcel MVP

NateO

Legend

Amileaux

Board Regular

Similar threads

Forum statistics

Share this page

Sumproduct in VBA problems

dk

MrExcel MVP

Excel Facts

just_jon

Legend

Barrie Davidson

MrExcel MVP

Richie(UK)

MrExcel MVP

dk

MrExcel MVP

NateO

Legend

Amileaux

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock