(Solved) – VBA Sumproduct returns #value! on cell – How do I fix my code to avoid this error?


I have a workbook that had links to about 80 other workbooks. It was really slow to load up. I decided to stop using links to speed it up. This meant finding a way to use VBA sumproduct to carry out some of the calculations. I have a headache now since I am getting #Value! error from my VBA code.

 option explicit

Sub VBA_Sumproduct

Dim rng_1 as range

Dim op_cell as range

Dim Model as String

Dim formula as string

Dim wb as workbook

dim wb2 as workbook

set wb2 = workbooks("wb2.xlsm")


set wb = workbooks("wb1.xlsm")

set rng_1=wb.sheets("A1").range("E2:E500")

set op_cell = wb2.sheets("T1").range("E20")

model = "Complete"

Formula = "sumproduct((rng_1="" & model & "")"

op_cell = application.evaluate(Formula)



end sub

Leave a Reply

Your email address will not be published. Required fields are marked *