why doesn't this countif work?

  • Thread starter Thread starter John
  • Start date Start date
J

John

NumberOfTriples = WorksheetFunction.CountIf(Myrange, "???")
The above gives NumberOfTriples as 1 which is incorrect


For Each cell In MyRange
If Len(cell) = 3 Then NumberOfTriples = NumberOfTriples + 1
Next
This one gives NumberOfTriples as 4 which is correct


The ranges are simple 3x3 or 5x5 like A1:C3

thanks

John
 
In the help, it looks like it should work, but I couldn't get it to work just
in a worksheet. I did get this to work in the worksheet when entered as an
array function.

=COUNT(IF(LEN(D6:D13)=3,D6:D13))

You may want to try something like that.

HTH,
Barb Reinhardt
 
Its numbers as text. Working on sudoku solver so numbers stored as text,
at least I hope so. The cells are all formated as text and every
variable holding the data is a string variable. All string manipulations
seem to work fine such as mid, len, trim etc. when working with the cell
as a range. The Find function works with "???" but not the countif
function. However the countif works with "?". I have one range,
WholeThing, which is the entire sudoku.

If WorksheetFunction.CountIf(WholeThing, "?")=81 then the sudoku is
solved. And that works just fine.

It's not such a big deal cause there's other ways but it's just a mystery.


John
 
That's more or less what I'm doing. It's just a mystery why countif
won't work. It works with a single "?".
 
I'd check the data first and also make sure that that variable was 0 when I
started.

Maybe you have leading/trailing spaces in one of your cells

if len(cell.value) = len(trim(cell.value)) then
'ok
else
msgbox cell.address & " has an extra space!"
end if

NumberOfTriples = 0
for each cell in myrng.cells
...
 
If WorksheetFunction.CountIf(WholeThing, "?")=81 then the
sudoku is solved. And that works just fine.
Click to expand...

Why not just use the CountA worksheet function? Assuming WholeThing is a Range...

If WorksheetFunction.CountA(WholeThing) = 81 Then
 
Back
Top