MS Excel Range for COUNTIF function in VBA code 1

[Ucaihc]

For a range, say H1:H35, I would like to use a COUNTIF function. However, the last row is determined within the application, so is not known in advance. How do I put this type of range in a COUNTIF function, i.e. when the range is H1 to H, LastRow+1.

 

[SkipVought]

Hi,

How about the entire column?

nCount = Application.Countif(range("H:H"), SomeValue)

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

nCount = Application.Countif(range("H1:H" & LastRow+1), SomeValue)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Ucaihc]

I am not sure I understand. Actually, i would like to go from H3 to the H35, where 35 is not set. It can be 35, or 46, depending on the data in the sheet. I do not want the whole column.

 

[SkipVought]

so there's other stuff in the column?

I'd try to avoid that kind of design.

Is the table of data you are looking at CONTIGUOUS?

If so...

with [H3].CurrentRegion
   lLastRow = .row + .rows.count - 1
end with

Skip,
[sub]
[red][/red]
[/sub]

 

[Ucaihc]

Ok, I will try the whole column, but it does not work no matter what. The application just hangs. What is wrong with this?

HCount = Application.CountIf(Range("H:H"), "*No*")

 

[SkipVought]

I copied. I pasted. I ran.

Are you SURE that this is the statement in error?

Skip,
[sub]
[red][/red]
[/sub]

 

[Ucaihc]

Yes. Actually the VBA program is in Outlook and is manipulating an Excel file. Should I try something else in place of the word Application?

 

[SkipVought]

Well if you are programming in Outlook VBA, you should have created an object for the Excel Application.

The Excel Application is the object of the Countif

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

As you use automation, besure to always qualify your excel objects:
HCount = [!]yourXLapp[/!].CountIf([!]yourXLapp.[/!]Range("H:H"), "*No*")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886