Sub or Function not defined

[tqm1]

Dear Experts

On userform1, when I call totals then compiler displays following error message
Sub or Function not defined

and debug window indicate to this line

F2 = CountIf(E, F1)

How to overcome this problem?



Sub TOTALS()

Dim FI As Long, F2 As Long, F3 As Long, F4 As Long, F5 As Long

With Sheets("weights")

F1 = Me.TextBox2.Value
F2 = CountIf(E, F1)
F3 = F5 = SumProduct((E = F1) * (N = 0) * 1)
F4 = SumIf(E, F1, M)
F5 = SumProduct((d = F1) * (M = 0) * 1)

Me.TextBox19.Value = F2
Me.TextBox20.Value = F3
Me.TextBox21.Value = F5
Me.Label23.Caption = CStr(F4)

End With

End Sub

[mikerickson]

This is one of the syntax's avaliable for using spreadsheet funticitons in VB.
(I'm assuming that in "F2 = CountIf(E, F1)", you meant all of column E)

F2 = Application.CountIf(Range("E:E"), F1)

F4 = Application.SumIf(Range("E:E"), F1, Range("M:M"))


I'm not sure if F4 is a good choice for a variable name, it might be confused with a cell address.
I don't use sumproduct enough to know how to translate those into VB code.

[Bob Phillips]

I don't use sumproduct enough to know how to translate those into VB code.

You can't, you have to use the dreaded evaluate



F5 = Activesheet.Evaluate("SumProduct(--(D2:D100=" & F1 & "),--(M2:M100=0))")