vba - Countif not working

[Mallesh23]

Hi Team,

I want to store apple count in variable,

formula works if I want to store output in range. but fails if I try to store in Variable. plz assist. thanks


applecount = "=COUNTIF(A1:A5,""Apple"")" 'GET Type mismatch.

Sub Countif()

Dim applecount As Long '

'AppleCount with Formula output in Range works
Range("D5").Formula = "=COUNTIF(A1:A5,""Apple"")"
Range("D5").FormulaR1C1 = "=COUNTIF(R1C1:R5C1,""Apple"")"


'How to Store Count in Variable
'AppleCount with Formula output in Variable not working.
applecount = "=COUNTIF(A1:A5,""Apple"")"  'GET Type mismatch.
applecount = "=COUNTIF(R1C1:R5C1,""Apple"")" ''GET Type mismatch.


'This works
applecount = WorksheetFunction.Countif(Range(Cells(2, 1), Cells(10, 1)), "Apple")
MsgBox applecount


End Sub

thanks
mg

 

[Peter_SSs]

Thanks once again , this also worked, learned something new today. ?

You're welcome.

In relation to other threads I try to look at as many as I can and if I come across yours and think I can help, I will.