Hi all,

I have a dozen of worksheets, in each worksheet I have a column that
contains an alpha code to convey statuses. For example, I can have a status
"R" fo ready, "C" for completed...etc.

Users of this workbook would go through each tab and every row and assign
status in the colum (say in column A) R, C or something else. But
sometimes, where there's no status, the users leave it blank (""). Other
times, a user error can cause a "" to become " " or any number of blank
spaces in the quote " ".

I need a way to count all cells in a given range in column A where their
statuses are not equal to "R" or "C". Since if the cell is not either one of
these, then user would know how many more rows needs t be updated with status
for all the worksheets in the workbook.

Is there a quick way of doing it? I can cycle through each tab and read row
by row and count them, but I thought there must be an Excel formula I can
take advantage of. Thanks in advance for sharing your thoughts.

Ben

--

Ben,

Here's a quick macro to count the number of cells that aren't "R" or "C":

Sub countRs_and_Cs()
Dim cell As Range, counter As Long
counter = 0
For Each cell In Selection
If Not (cell = "R" Or cell = "C") Then
counter = counter + 1
End If
Next cell
MsgBox counter & " more rows need to be entered"
End Sub

The limitation of this macro is that a selection must be highlighted before
running it (as opposed to determining the best range programmatically). You
could modify the macro if you have a predictable range each time.

Otherwise, you could use an Excel function like:

=COUNTIF($A$1:$A$20,"=R")+COUNTIF($A$1:$A$20,"=C")

The problem with this is that you have to enter a function each time.

My reccommendation is that if there is a predictable pattern to your data
(that is, a column that always has a value), use that to determine the size
of your range and pass that range to the above macro. I think that the macro
is a little bit easier than the function, specifically since you have two
values to count and COUNTIF isn't great with logical operators.

Let me know if this works.

HTH,
Pflugs

"Ben" wrote:

> Hi all,
>
> I have a dozen of worksheets, in each worksheet I have a column that
> contains an alpha code to convey statuses. For example, I can have a status
> "R" fo ready, "C" for completed...etc.
>
> Users of this workbook would go through each tab and every row and assign
> status in the colum (say in column A) R, C or something else. But
> sometimes, where there's no status, the users leave it blank (""). Other
> times, a user error can cause a "" to become " " or any number of blank
> spaces in the quote " ".
>
> I need a way to count all cells in a given range in column A where their
> statuses are not equal to "R" or "C". Since if the cell is not either one of
> these, then user would know how many more rows needs t be updated with status
> for all the worksheets in the workbook.
>
> Is there a quick way of doing it? I can cycle through each tab and read row
> by row and count them, but I thought there must be an Excel formula I can
> take advantage of. Thanks in advance for sharing your thoughts.
>
> Ben
>
> --
>