Concatenate values into range VBA

[Drawleeh]

Hello, I have the following code that I can't figure out how to adjust -

Sub SumData()

Dim HSArr(2 To 250) As Variant
Dim HSVal As Long

For HSVal = LBound(HSArr) To UBound(HSArr)
HSArr(HSVal) = Cells(HSVal, 1) & "_" & Cells(HSVal, 2)
Next HSVal


    Const dFormula As String _
        = "=IFERROR(SUMPRODUCT(--(HSArr=I2),D$2:D$250,F$2:F$250),"""")"
    With ThisWorkbook.Worksheets("Sheet1").Range("K2:K250")
        .Formula = dFormula
        .Value = .Value
    End With

End Sub

All I want to do is concatenate column A and column B and then parse that A:B as range into the SUMPRODUCT without having to express the concatenated range onto the worksheet . Currently nothing happens when code is run, what am I doing wrong?

 

[bebo021999]

With this:

For HSVal = LBound(HSArr) To UBound(HSArr)
HSArr(HSVal) = Cells(HSVal, 1) & "_" & Cells(HSVal, 2)
Next HSVal

I belive HSArr does not save history of previous HSVal
Try to add
Msgbox HSArr(250)
to see the instant result.

 

[Drawleeh]

My issue is that the array concatenates the 2 values fine, I can take the array and populate it into the column, but I don't know how to express that as a range in the SUMPRODUCT without actually assigning an actual range on the worksheet? Currently the array is storing the data but not in the required format for the SUMPRODUCT to be used. How do I include the first part of my code to work with the second or do I have to do something completely different..?