Need help with COUNTIF in VBA

[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.

 

[Tom Ogilvy]

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

or use WorksheetFunction rather than Application

 

[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")

 

[Bob]

Tom,
Thanks for your help!
Bob

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

or use WorksheetFunction rather than Application

 

[Bob]

Vergel,
Thanks for your help!
Bob

Bob,

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

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

 

[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

 

[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")

 

[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