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

 

[ShuStar]

Hmm.. Try dim applecount as integer or as variant.

 

[Mallesh23]

Hi Shustar,

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

Thanks
mg

 

[ShuStar]

Try
Dim rng As Range
Dim countcell As Range
Dim criteria As String
Set rng = Range("A1:A5")
Set countcell = Range("D1") 'can be any cell but not a1:a5

criteria = "apple"
Countcell = WorksheetFunction.CountIf(rng, criteria)

 

[ShuStar]

Or try below - (sticking to your own code but write as below)

Dim applecount as integer

Applecount = Application.WorksheetFunction.CountIf(Range("A1:A5"), "apple")

 

[Mallesh23]

Hi ShuStar,

Thanks for your help that method worked.
But I would like to know alternative option as well.

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


Thanks
mg

 

[Peter_SSs]

I would like to know alternative option as well.

Dim applecount As Long

applecount = Evaluate("COUNTIF(A1:A5,""Apple"")")

 

[Mallesh23]

Hi Peter,

Thank you so much, above trick worked.

But in R1C1 Case it didn't
applecount = Evaluate("COUNTIF(R1C1:R5C1,""Apple"")")

What evaluate does?..

Thanks
mg

 

[Peter_SSs]

You will likely run into more problems using R1C1 notation with Evaluate, particularly if using relative addresses. Much simpler to use the A1 style references.

However, for this particular example

applecount = Evaluate(Application.ConvertFormula("=COUNTIF(R1C1:R5C1,""Apple"")", xlR1C1, xlA1))

 

[Mallesh23]

Hi Peter,

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

Last time you helped me in dictionary and Array combination.

I have similar task, This time needs to merge two seperate array into Single Array and print the result.
Awaiting some one can help me on this.


My query lies with title. however it was duplicate posting. This one is clear to undestand my requirement.
vba - Replace multiple vlookup with Dictionary and Array


original posting with title...
VBA array help-




Regards,
mg