SumProduct in VBA

[twcheung36]

Hello everyone,

I am a beginner and start using VBA two weeks ago. Currently, I would like to replace excel functions with VBA for the work.
However, I'm getting stuck in SumProduct functions. Hope some experts could help me to figure out what's wrong in the following codes.

The similar excel functions that I had and works:
=SUMPRODUCT(('753-HL'!AJ$8:AJ$64)*('753-HL'!$BG$8:$BG$64=STATS!$D17)*(ISNUMBER(SEARCH("D",'753-HL'!$BF$8:$BF$64))))

---

#Attempt 1
Worksheets("Sheet1").Range("D17:D31").Value = Application.WorksheetFunction.SumProduct = (Sheets("757").Range("AU9:AU26") * (Worksheets("757").Range("S9:S26") = Worksheets("Sheet1").Range("C17:C31")) * (IsNumeric(Sheets("757").Range("X9:X26"), "D")))

For this code, I got a Compile error: Argument not optional which underlines as yellow of the .SumProduct

---

#Attempt 2
Worksheets("Sheet1").Range("D17:D31").Value = Application.SumProduct = (Sheets("757").Range("AU9:AU26") * (Worksheets("757").Range("S9:S26") = Worksheets("Sheet1").Range("C17:C31")) * (IsNumeric(Sheets("757").Range("X9:X26"), "D")))

For this code, I got a Wrong number of arguments or invalid property assignment which underlines as yellow of the IsNumeric#Attempt 3

---

#Attempt 3
Dim WF As WorksheetFunction
Dim a As Range, b As Range, c As Range
Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant
Dim i As Integer
Set WF = Application.WorksheetFunction
Set a = Worksheets("757").Range("AU9:AU26")
Set b = Worksheets("757").Range("S9:S26") = Worksheets("Sheet1").Range("C17:C31")
Set c = --IsNumber(Worksheets("757").Range("X9:X26"), "D")
Arr1 = WF.Transpose(a)
Arr2 = WF.Transpose(b)
Arr3 = WF.Transpose(c)
'Range("D17").Value = WF.SumProduct(Arr1, Arr2, Arr3)

For this code, I got a Sub or Function not defined which underlines as yellow of the IsNumber

---

Highly appreciated if someone could help, Thank you.

 

[ukmikeb]

Hi

Welcome to the MrExcel forum.

Try:-

wx = "753-HL"
Range("D17").Value = Evaluate("SUMPRODUCT(('" & wx & "'!AJ$8:AJ$64)*('" & wx & "'!$BG$8:$BG$64=STATS!$D17)*(ISNUMBER(SEARCH(""D"",'" & wx & "'!$BF$8:$BF$64))))")

based on your original formula.

hth

 

[twcheung36]

Hi

Welcome to the MrExcel forum.

Try:-

wx = "753-HL"
Range("D17").Value = Evaluate("SUMPRODUCT(('" & wx & "'!AJ$8:AJ$64)*('" & wx & "'!$BG$8:$BG$64=STATS!$D17)*(ISNUMBER(SEARCH(""D"",'" & wx & "'!$BF$8:$BF$64))))")

based on your original formula.

hth

ukmikeb,

Thank you so much! Your code works in VBA. However, after I applied this code for several cells, let say like D17, D18, D20. An error 9 appeared and said: Subscript out of range. May I ask how to solve this error? It didn't exist before. Nevertheless, thank you for the previous solution!!

 

[ukmikeb]

Hi

How did you apply the code and to which cells?

If you had the original formula in cells D17, D18 and D20 how do the relative addresses change?

What is your objective in changing to vba from formulae?

What piece of code do you get the subscript error 9?