Type mismatch problem?

[NooK]

I am having a big problem.

I have the following loop

FlCellCount = 2
IntEnd = 100
Do While newBook.Worksheets(Sh).Cells(FlCellCount, 2) < (IntEnd
1000)
IntSizeCnt = IntSizeCnt + 1
FlCellCount = FlCellCount + 1
Loop

Now everything has worked fine for like a month but I found that thi
is causing problems on a particular data.

I have a book with 2 worksheets and basically this loop will go throug
one of the sheets and check, line by line, which ones are under 100000.

It has worked fine until I tried in a sheet with 4 rows of values, wha
happens is that it'll start looping and keep on going (And not qui
after FlCellCount = 6 since after line 5 the whole sheet is full o
empty cells) until it jumps out of the sub (Instead of going further
and going to the line under the Sub call.

I tried debugging and if I stop the mouse over the "Cells" nothing i
shown (instead of showing the value for that exactly cell).

Now I changed the Cells part to a Range, and now when I stop the mous
over it (Upon debugging) it shows "Type mismatch".

There is nothing wrong with the sheet, column B contains values lowe
than 100000 (First value is 15505 in B2) and as I said it only happen
to a specific sheet, all other sheets I tried (With the exact same kin
of data) works fine and the loop stops upon finding a line with a valu
over 100000 and the sub continues.

Anybody got an idea of what could be happening?

Best Regards

Noo

 

[mike]

Empty cells value is inferior to any numerical positive
value.
If you don't have any numerical value greater than 100000
till you reach empty cells your loop won't finish ever.

Which was exactly the change of "cells" with "range"?

 

[NooK]

Thanks, the change was

From: Cells(FlCellCount, 2)

To: Range("B" + FlCellCount)

I am gonna look into the endless loop thing and check for empty a
well.

Will an Empty cell trigger the IsEmpty method or do I have to check fo
""?

Best Regards

Noo

 

[Tom Ogilvy]

Dim sStr as String
FlCellCount = 2
IntEnd = 100
Do While newBook.Worksheets(Sh).Cells(FlCellCount, 2) _
< (IntEnd *1000)
sStr = NewBook.Worksheets(sh).Cells(FlCellcount,2).Text
sStr = Application.Substitute(sStr,chr(160)," ")
if len(Trim(sStr)) = 0 then exit do
IntSizeCnt = IntSizeCnt + 1
FlCellCount = FlCellCount + 1
Loop