Sumproduct gives value error

[fraufreda]

Good day to you all
The sumproduct formula is working perfect but since ranges I set in the code are dynamic I am facing a problem to get it work. I tried to google for a solution but without success. I hope you can help. Here is part of the code

Dim PyFields, Amounts, rng As Range
Dim ReportSht As Worksheet

Set ReportSht = Sheets("PR report")

With ReportSht
    Set PyFields = .Range(.Cells(2, 4), .Cells(2, LC))
    Set Amounts = .Range(.Cells(3, 4), .Cells(LR, LC))
End With

For each rng in Range(xxxx)
rng.Offset(0,1).Value = Application.Evaluate("SumProduct((" & PyFields.Address & " = """ & rng.Value & """)," & Amounts.Address & ")")
next

 

[Skyybot]

Set PyFields = Application.InputBox("Select range.", "PyFields Range", Type:=8)
Set Amounts = Application.InputBox("Select range.", "Amounts Range", Type:=8)

 

[Fluff]

Maybe

For Each rng In Range(xxxx)
rng.Offset(0, 1).Value = ReportSht.Evaluate("SumProduct(--(" & PyFields.Address & " = """ & rng.Value & """)," & Amounts.Address & ")")
Next

 

[fraufreda]

Set PyFields = Application.InputBox("Select range.", "PyFields Range", Type:=8)
Set Amounts = Application.InputBox("Select range.", "Amounts Range", Type:=8)

thank you but I don't want to select. I want the code to run without any user selection

 

[fraufreda]

Maybe

For Each rng In Range(xxxx)
rng.Offset(0, 1).Value = ReportSht.Evaluate("SumProduct(--(" & PyFields.Address & " = """ & rng.Value & """)," & Amounts.Address & ")")
Next

No it didn't work

 

[Fluff]

In that case can you post some sample data.

 

[Skyybot]

Could you put your PyFields and Amounts data into a Table and refer to the Tables? That way the Range of the Table could be called using:

Range(ActiveSheet.ListObjects("TableName")).Address

And Tables are dynamic.

 

[fraufreda]

Got it. The problem was with the ranges that were set. Their addresses were not referenced to the sheet.

 

[Fluff]

Their addresses were not referenced to the sheet

They don't need to be, as long as the evaluate is qualified with the sheet as I showed.

 

[Anthony47]

I see three problems with the OP code:
1) Yes, in both the terms in SumProducts there was a "NameOfTheSheet!" missing
2) Yes, the "--" is missing
3) The two terms in SumProduct have different leght, as the first term is 1 Row * N column, whereas the second has (LR-3) Rows * N Columns

Anyway, glad that he found a working solution