Evaluate sumproduct function with range variable

[walduxas]

Hi,

I have recently started to use Evaluate function so I'm not quite familiar with how it works. I managed to get this code working:

Kr = """" & Cr & """"
Kr2 = """" & Cr2 & """"
Sum = Application.Evaluate("=SUMPRODUCT((Aug12!L2:L20=" & Kr & ")*(Aug12!M2:M20=" & Kr2 & ")*(Aug12!AS2:AS20))")

However, I'd like to change range address text in this function and use additional variables instead of them. It could look something like this, but I can't managed to get it working:

Kr = """" & Cr & """"
Kr2 = """" & Cr2 & """"
CheckCrNr = 12
CheckCrNr2 = 13
SumCrNr = 45
With ThisWorkbook.Worksheets("Aug12")
Sum = Application.Evaluate("=SUMPRODUCT((" & .Columns(CheckCrNr) & "=" & Kr & ")*(" & .Columns(CheckCrNr2) & "=" & Kr2 & ")*(" & .Columns(SumCrNr) & "))")
End With

Do you have any ideas?

[walduxas]

Considering that no one could help on this case, as usually I solved this by myself. Here is the solution (in this example StartRow, StartCol, and HowMany variables' values are set in advance, I don't give their value in this case):

shtName = "Aug12"
Cr = "Criteria1"
Cr2 = "Criteria2"

with ThisWorkbook.worksheets(shtName)
    Rng1 = .Range("A1").Offset(StartRow - 1, StartCol1 - 1).Resize(HowMany, 1).AddressLocal
    Rng2 = .Range("A1").Offset(StartRow - 1, StartCol2 - 1).Resize(HowMany, 1).AddressLocal
    Rng3 = .Range("A1").Offset(StartRow - 1, StartCol3 - 1).Resize(HowMany, 1).AddressLocal
    Sum = Application.Evaluate("=SUMPRODUCT(('" & shtName & "'!" & Rng1 & "=" & Chr(34) & Cr & Chr(34) & ")*('" & shtName & "'!" & Rng2 & "=" & Chr(34) & Cr2 & Chr(34) & ")*('" & shtName & "'!" & Rng3 & "))")
end with

Important thing to consider: if in chosen range Rng3 are any non numeric values, then evaluate formula returns an error. In my case there were column names in the first row, so I needed to include StartRow variable, which is equal to the next row number below column names row (so that only numeric values would be included in a range).