SumProduct in VBA

Question

I am trying to count the cells in range where the length of the cell is less than 2.

Regular formula "sumproduct(n(len(A2:D10)<2))" works as expected. while trying to incorporate this in my code

if [sumproduct(n(len(A2:D10)<2))] <> 0 then 
else
end if

it works, but the problem is that range is not fixed so whenever i try to assign a variable and run it is not working shows Error 2029


if [sumproduct(n(len("A2:D" & lrow)<2))] <> 0 then 
else
end if

anyway to get around this issue.

Thanks.

`application.WorksheetFunction.SumProduct(x,y,z)` for example — Nathan_Sav, Mar 12 '20 at 15:45
You might want to look at: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — MrDogme, Mar 12 '20 at 15:49
You will need to use `worksheets().Evaluate() ` instead `[]` — Scott Craner, Mar 12 '20 at 16:25
`if ActiveSheet.Evaluate("sumproduct(n(len(A2:D" & lrow & ")<2))") <> 0 then ` — Scott Craner, Mar 12 '20 at 18:17

score 0 · Accepted Answer · answered Mar 13 '20 at 03:13

0

Thanks Scott -

I thought Activesheet was not needed so i used []

ActiveSheet.Evaluate("sumproduct(n(len(A2:D" & Lrow & ")<2))")

Now i have modified this as per my requirement I was able to overcome my problem.

Thanks a lot.

answered Mar 13 '20 at 03:13

Prince

1 Answers1