sumproduct issue in VBA 1

[krispi]

Hi all - can anyone explain why the following code is returning an error2015 against the 'myVar=' expression?

The SumProduct expression is valid when entered in the spreadsheet as a formula. I thought I'd found the answer in thread707-1158104, hence the Evaluate command, but no joy. All is well in the Watch window up until the 'myVar='

Any ideas would be really helpful!

Just trying to get one cell working at the moment - here's the code...

Private Sub CommandButton1_Click()

Dim wbTemplate As Workbook
Dim wb1 As Workbook
Dim strLUProd As String
Dim intLookUpValue1 As Integer
Dim strLookUpValue2 As String
Dim rngLookUpRange1 As Range
Dim rngLookUpRange2 As Range
Dim rngLookUpRange3 As Range
Dim intLookUpCol As Integer
Dim intR As Integer
Dim intC As Integer

    intR = 8
    intC = 2
    intLookUpCol = 2

    Set wbTemplate = ThisWorkbook
    Set wb1 = Workbooks.Open(wbTemplate.Worksheets("Source Data Files").Range("A5").Text)
    
    strLUProd = wbTemplate.Worksheets("Arrears").Cells(2, intC)
    Set rngLookUpRange1 = wb1.Worksheets("Possessions by Product-Months").Range("A6:A1000")
    Set rngLookUpRange2 = wb1.Worksheets("Possessions by Product-Months").Range("B6:B1000")
    Set rngLookUpRange3 = wb1.Worksheets("Possessions by Product-Months").Range("C6:C1000")
    
    myVar = Application.Evaluate("SumProduct((rngLookUpRange1 = strLUProd) * (rngLookUpRange2 = wbTemplate.Worksheets(""poss"").cells(intR,1)) * (rngLookUpRange3))")
    wbTemplate.Worksheets("poss").Cells(intR, intC).Value = myVar

End Sub

Thanks folks,

Chris

 

[ck1999]

I would guess it is because of the myvar statement having 2 equals (=) in it.

What exactly do you want this line of code to do? It is very confusing.

Chris

 

[AnotherHiggins]

krispi: Try separating sections of the formula by commas rather than multiplying them (as you did in the worksheet formula).

ck1999: this discussion started in thread68-1494319. The 3 equals signs are expected - two of them are within the SumProduct formula.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[krispi]

John - thanks for posting, but unfortunately still the same result. Interestingly, the code isn't erroring out, it runs through as a valid routine, it's just the result that resolves to Error.

Any further help would be gratefully received!

 

[GlennUK]

krispi,

The SumProduct expression is valid when entered in the spreadsheet as a formula

You have replaced whatever arguments you had with VBA dimensions objects, inside a string. I can't see how Evaluate would possible interpret that. The Evaluate example you spotted elsewhere used defined worksheet names, and so the formula was exactly the same as you'd find in the worksheet.

I'll try to work out what will work for you.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[krispi]

Thanks Glenn, what you say makes perfect sense - appreciate your help.

 

[GlennUK]

Have a look at this:

Private Sub CommandButton1_Click()
Dim wbTemplate As Workbook
Dim wb1 As Workbook
Dim strLUProd As String
Dim intLookUpValue1 As Integer
Dim strLookUpValue2 As String
Dim intLookUpCol As Integer
Dim intR As Integer
Dim intC As Integer

    intR = 8
    intC = 2
    intLookUpCol = 2

    Set wbTemplate = ThisWorkbook
    Set wb1 = Workbooks.Open(wbTemplate.Worksheets("Source Data Files").Range("A5").Text)
    
    strLUProd = wbTemplate.Worksheets("Arrears").Cells(2, intC)
    
    ActiveWorkbook.Names.Add Name:="Range1", RefersToR1C1:= _
        "='Possessions by Product-Months'!R6C1:R1000C1"
    ActiveWorkbook.Names.Add Name:="Range2", RefersToR1C1:= _
        "='Possessions by Product-Months'!R6C2:R1000C2"
    ActiveWorkbook.Names.Add Name:="Range3", RefersToR1C1:= _
        "='Possessions by Product-Months'!R6C3:R1000C3"

    myVar = Application.Evaluate("SumProduct((Range1=" & Chr(34) & strLUProd & Chr(34) _
            & ")*(Range2=" & Chr(34) & wbTemplate.Worksheets("poss").Cells(intR, 1) _
            & Chr(34) & ")*(Range3))")
    wbTemplate.Worksheets("poss").Cells(intR, intC).Value = myVar

End Sub

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[krispi]

Thanks for that Glenn - I think it's getting there! It's no longer returning the error message, but everything now resolves to zero (incorrectly). Could you talk me through the significance of the Chr(34)s in the string - I might then be able to work out what to do to fix it?

Thanks again,

Chris

 

[GlennUK]

The Chr(34)'s put " around items that are being tested as strings ... it might be that you are testing for numbers though. You could remove them and try again.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[krispi]

Glenn - absolutely spot on! I removed the chr(34)s from the right hand side of the 'Range2=' part of the expression, as it was looking for numbers.

It now works perfectly - thanks for your time and effort, have a star!