SUMPRODUCT in VBA - several tries without success

[Instantaneo]

Hi again,

I managed to understand that the WorksheetFunctions.SumProduct only works in VBA when you have simple arrays and not conditions. Then I figured out (by other threads in this forum) that "Evaluate" function could get good results.

However, all the tests I'm doing (even with "stupid useless" intermediate steps like msgboxes to show me what's happening) are getting #VALUE...

Please help me find the mistake here... Maybe it's useful to say that some of the ranges are within a pivot table...

Sub UpdateTables()

    Dim pivot_total As Long
    Dim range_pivot As String
    Dim ws As Worksheet
    Dim i As Integer
    Dim mysp As String


    Set ws = Worksheets("KPI_Node")
    ws.Select
    
    'Table Ranking LOS
    pivot_total = Application.WorksheetFunction.Match("Grand Total", ws.range("X:X"), 0)
    range_pivot = "AD7:AD" & (pivot_total - 1)
       
    For i = 1 To 20
        mysp = "=SUMPRODUCT(--(AD7:AD" & (pivot_total - 1) & "=" & ws.range("Af" & i + 36).Value & "); _
                    --(X7:X" & (pivot_total - 1) & "=" & ws.range("Ah" & i + 36).Value & "); _
                    (AA7:AA" & (pivot_total - 1) & "))"
        MsgBox mysp
        ws.range("AI" & i + 36).Value = Application.Evaluate(mysp)

End Sub

 

[Instantaneo]

Please, can anyone help me with this?

 

[yky]

I did not get so far to get #VALUE. I could not compile because there is FOR but no NEXT.

 

[Instantaneo]

I missed copying the NEXT into the thread (when I deleted other unnecessary lines), but it is in the code

 

[yky]

Where should the NEXT be?

Did the message box show the desired formula?

 

[Instantaneo]

Where should the NEXT be?

Did the message box show the desired formula?

The code I should have copied was:

Sub UpdateTables()

    Dim pivot_total As Long
    Dim range_pivot As String
    Dim ws As Worksheet
    Dim i As Integer
    Dim mysp As String


    Set ws = Worksheets("KPI_Node")
    ws.Select
    
    'Table Ranking LOS
    pivot_total = Application.WorksheetFunction.Match("Grand Total", ws.range("X:X"), 0)
    range_pivot = "AD7:AD" & (pivot_total - 1)
       
    For i = 1 To 20
        mysp = "=SUMPRODUCT(--(AD7:AD" & (pivot_total - 1) & "=" & ws.range("Af" & i + 36).Value & "); _
                    --(X7:X" & (pivot_total - 1) & "=" & ws.range("Ah" & i + 36).Value & "); _
                    (AA7:AA" & (pivot_total - 1) & "))"
        MsgBox mysp
        ws.range("AI" & i + 36).Value = Application.Evaluate(mysp)
    Next i
End Sub

And the MsgBox seems to be delivering exactly the formula I wanted:

 

[yky]

Can't see anything wrong with the formula.There are three arrays in the SUMPRODUCT formula. Try SUMPRODUCT with only one of them and see which one is causing the problem.

 

[Instantaneo]

Sorry for only getting back to this today (other urgent issues came at work).


After deleting each condition one by one, I remembered an answer I had in another topic regarding the language settings. So I thought: even if "," is my decimal separator and ";" my formula separator, VBA seems to understand "," and "." as separators, respectively.


So, the right answer was...

mysp = "=SUMPRODUCT(--(AD7:AD" & (pivot_total - 1) & "=" & Replace(ws.range("Af" & i + 36).Value, ",", ".") & "),--(X7:X" & (pivot_total - 1) & "=" & ws.range("Ah" & i + 36).Value & "),(AA7:AA" & (pivot_total - 1) & "))"

Thank you for all your help!