SUMPRODUCT code does nothing?

[Drawleeh]

I have the following code, I am trying to use the SUMPRODUCT function, I want to loop through all the concatenated values in A and B and then use that info in the SUMPRODUCT. But my code isn't doing anything or throwing up any errors. I don't see why it doesn't work?

Sub SumData()
Dim val As Long
Dim Concat(2 To 250) As Variant

For val = 2 To 250
Concat(val) = Cells(val, 1) & " " & Cells(val, 2)

Evaluate ("=SUMPRODUCT(--(Concat(Val)=Cell(Val,9)),Cell(Val,4),Cell(Val,6))")
With ThisWorkbook.Worksheets("Sheet1").Range("K2:K250")
End With
Next val
    
End Sub

 

[jasonb75]

The correct syntax would be something like this. Note that I've changed the name of the array from Concat to cArray. Excel contains a function called Concat so you are at risk of conflict errors.

Sub SumData()
Dim val As Long
Dim cArray(2 To 250) As Variant

For val = 2 To 250
    cArray(val) = Cells(val, 1) & " " & Cells(val, 2)
   
    ThisWorkbook.Worksheets("Sheet1").Cells(val, 11).Value = Evaluate ("=SUMPRODUCT(--(" & cArray(Val) & "=" & Cells(Val,9) & ")," & Cells(Val,4) & "," & Cells(Val,6) & ")")
Next val
    
End Sub

 

[Fluff]

Duplicate to: Concatenate values into range VBA

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.