Need help with COUNTIF in VBA

B

Bob

I'm getting an error message when the following line attempts to execute:

x = CountIf(Worksheets(Sheet1Name).Range("H" & Sheet1RowPointer & ":U" &
Sheet1RowPointer), ">0")

Sheet1Name is Dim'd as String. Sheet1RowPointer is "Dim'd as Integer.

I'm simply trying to count the number of cells that contain a value >0 for a
given row between columns H & U, inclusive, and assign the result to "x".

Can someone tell me how to fix the aforementioned line so it will work
correctly?

Thanks in advance for the help.
 
T

Tom Ogilvy

x = Application.CountIf(Worksheets(Sheet1Name) _
.Range("H" & Sheet1RowPointer & ":U" & _
Sheet1RowPointer), ">0")

or use WorksheetFunction rather than Application
 
V

Vergel Adriano

Bob,

You need to qualify CountIf with "Worksheet.". Try it like this

x = WorksheetFunction.CountIf(Worksheets(Sheet1Name).Range("H" &
Sheet1RowPointer & ":U" & Sheet1RowPointer), ">0")
 
B

Bob

Tom,
Thanks for your help!
Bob


Tom Ogilvy said:
x = Application.CountIf(Worksheets(Sheet1Name) _
.Range("H" & Sheet1RowPointer & ":U" & _
Sheet1RowPointer), ">0")

or use WorksheetFunction rather than Application
 
B

Bob

Vergel,
Thanks for your help!
Bob


Vergel Adriano said:
Bob,

You need to qualify CountIf with "Worksheet.". Try it like this

x = WorksheetFunction.CountIf(Worksheets(Sheet1Name).Range("H" &
Sheet1RowPointer & ":U" & Sheet1RowPointer), ">0")
 
B

Bob

Tom,
One more question:

Is it possible to somehow use R1C1 nomenclature for the Range? So instead of:

x = WorksheetFunction.CountIf(Worksheets(Sheet1Name) _
.Range("H" & Sheet1RowPointer & ":U" & _
Sheet1RowPointer), ">0")

can I do something like this:

x = WorksheetFunction.CountIf(Worksheets(Sheet1Name) _
.Range(Cells(Sheet1HoursStartCol, Sheet1RowPointer) & _
":" & Cells(Sheet1HoursEndCol, Sheet1RowPointer)), ">0")

Thanks again for your help.
Bob
 
T

Tom Ogilvy

I would do it this way

Dim r as Range, x as Long
With Worksheets(Sheet1Name)
set r = .Range(.Cells(Sheet1HoursStartCol, Sheet1RowPointer), _
.Cells(Sheet1HoursEndCol, Sheet1RowPointer))
End with
x = WorksheetFunction.CountIf(r, ">0")


but you can do it with your original
x = WorksheetFunction.CountIf(Worksheets(Sheet1Name) _
.Range(Cells(Sheet1HoursStartCol, Sheet1RowPointer).Address & _
":" & Cells(Sheet1HoursEndCol, Sheet1RowPointer).Address), ">0")
 
B

Bob

Tom,
As always, thanks for your help! I tried using my proposed formula (which I
noticed you added "Address" to):

x = WorksheetFunction.CountIf(Worksheets(Sheet1Name) _
.Range(Cells(Sheet1HoursStartCol, Sheet1RowPointer).Address & _
":" & Cells(Sheet1HoursEndCol, Sheet1RowPointer).Address), ">0")

but "x" yielded 0 (zero). When I reverted back to my original formula:

CellsFound = WorksheetFunction.CountIf(Worksheets(Sheet1Name) _
.Range("H" & Sheet1RowPointer & ":U" &
Sheet1RowPointer), ">0")

x yielded 2, which is the correct answer. Do you have any idea why the
first formula above is not yielding the correct answer?

Thanks again,
Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top