Need help with COUNTIF in VBA
 

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.

Need help with COUNTIF in VBA
 

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

or use WorksheetFunction rather than Application

--
Regards,
Tom Ogilvy

"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

Bob,

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

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


--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

Tom,
Thanks for your help!
Bob


"Tom Ogilvy" wrote:

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

or use WorksheetFunction rather than Application

--
Regards,
Tom Ogilvy

"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

Vergel,
Thanks for your help!
Bob


"Vergel Adriano" wrote:

Bob,

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

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


--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

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" wrote:

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

or use WorksheetFunction rather than Application

--
Regards,
Tom Ogilvy

"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

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


--
Regards,
Tom Ogilvy


"Bob" wrote:

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" wrote:

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

or use WorksheetFunction rather than Application

--
Regards,
Tom Ogilvy

"Bob" wrote:

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.

Need help with COUNTIF in VBA
 

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


"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy


"Bob" wrote:

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" wrote:

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

or use WorksheetFunction rather than Application

--
Regards,
Tom Ogilvy

"Bob" wrote:

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.