VBA countif

Tom F

Active Member
Joined
Oct 19, 2002
Messages
263
=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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,914
Code:
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

Active Member
Joined
Oct 19, 2002
Messages
263
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

Code:
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

Active Member
Joined
Oct 19, 2002
Messages
263

ADVERTISEMENT

It worked like a charm hotpepper.

Thanks !!!!!!!!!
Tom
 

tt.viquel

New Member
Joined
Apr 14, 2011
Messages
30
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top