How to count blanks and spaces that look like blanks

[Ben]

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

--

 

[Pflugs]

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