Instantaneo
New Member
- Joined
- Dec 5, 2012
- Messages
- 28
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...
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...
Code:
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
Last edited: