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

John,

What sort of data do you have in the range? COUNTIF(range,"???") will
only count text not numbers, if you want to count cells with 3
characters with a formula

=SUMPRODUCT(--(LEN(range)=3))


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134051

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


barry houdini wrote:
John,

What sort of data do you have in the range? COUNTIF(range,"???") will
only count text not numbers, if you want to count cells with 3
characters with a formula

=SUMPRODUCT(--(LEN(range)=3))

You need to be careful when you format cells as text. If you enter a
number in a cell and then format as text afterwards then the cell format
will say text.....but it isn't. You can check by using ISTEXT worksheet
function.

Usually COUNTIF counts text and numbers so

=COUNTIF(A:A,1)

will count any cells in column A that contain a 1 whether it's text
formatted or not, but as soon as you introduce a wildcard, numbers
aren't included so

=COUNTIF(A:A,"1?")

won't count 12 (unless it's text-formatted) but will count 1x


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134051

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

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

If WorksheetFunction.CountA(WholeThing) = 81 Then

--
Rick (MVP - Excel)


"John" wrote in message ...
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


barry houdini wrote:
John,

What sort of data do you have in the range? COUNTIF(range,"???") will
only count text not numbers, if you want to count cells with 3
characters with a formula

=SUMPRODUCT(--(LEN(range)=3))

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

"John" wrote:

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

That's more or less what I'm doing. It's just a mystery why countif
won't work. It works with a single "?".

Barb Reinhardt wrote:
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

"John" wrote:

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

Hi John,

Take a look to Dermot Balson's page.

http://www.westnet.net.au/balson/Mod...werTools.shtml

Link - Sudoku -a workbook to solve it, give hints, even create new puzzles
of varying difficulty

Wkr,

JP

"John" wrote in message
...
That's more or less what I'm doing. It's just a mystery why countif won't
work. It works with a single "?".

Barb Reinhardt wrote:
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

"John" wrote:

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

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

John wrote:

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

--

Dave Peterson