VBA countif

[Tom F]

=countif(d12:d100,”apple”)

How would this statement be coded in VBA changing d100 to the first non-blank cell from the bottom of column d?

Thanks for your time and help,
Tom

 

[mikerickson]

Dim xRange as Range
Set xRange=Cells(Rows.Count,4).End(xlup)
Set xRange=Range(Range("d12"),xRange)
answer=Application.CountIf(xRange,"apple")

 

[Tom F]

mikericson,

I must have made a mistake for there was a Run-Time error '438'. Object doesn't support this property or method in the third line after I placed a Sub / End Sub around your code and ran it.

Also, I need the 'answer' to appear in cell D6.

Many thanks,
Tom

 

[Scott Huish]

Perhaps:

Sub test()
Dim x As Long
x = Range("D" & Rows.Count).End(xlUp).Row
If x < 12 Then x = 12
Range("D6") = Application.WorksheetFunction.CountIf(Range("D12:D" & x), "apple")
End Sub

 

[Tom F]

It worked like a charm hotpepper.

Thanks !!!!!!!!!
Tom

 

[tt.viquel]

hi,

I am having a similar problem, how could i do a countif in sheet 5, range G:G counting all the Y and placing them in sheet 1 column D.

Also I have multiple countif's doing the same thing in 5 other worksheets, but they look at different ranges e.g. A:D could i possibly do this in one sub or i have to do different subs for each.

 

[dario galasso]

Thank you for your help, now I have solved my problem. Dario