Code works 1st time then Type Mismatch when checking for not blank cells

  • Thread starter Thread starter Colleyville Alan
  • Start date Start date
C

Colleyville Alan

I have some VBA code writing info to a spreadsheet. At one point, I loop
from row 200 backwards looking for a row with text so that I can indicate
that this is the last row.
After I have written a bunch of text, this runs and everything is fine. But
then I add some more text and I get a "Type Mismatch" error.


For n = 200 To 1 Step -1
If oxlWks.Worksheets("Sheet1").Range("B" & n) Or
oxlWks.Worksheets("Sheet1").Range("A" & n) <> "" Then
ilastrow = n
Exit For
End If
Next n

As I say, it goes thru fine but the next time I run it, it crashes on the
line that checks for a not empty condition. The text is bold but that is
the only difference between the type of text that was there the first time
through. How in the world do I get a "Type Mismatch"? I have looked at a
watch window and it shows just what I see on the spreadsheet. I know a
crude work-around, but this is bugging me.

Thanks

p.s. if there is a lastrow function in Excel, fine. But I still want to
know why this does not work.
 
Why not do away with your loop and use

ilastrow = oxlWks.Worksheets("Sheet1").Range("A65536").End(xlUp).Row


this gets the last row with an entry in column a



Can not help you as to why your code fail
 
mudraker > said:
Why not do away with your loop and use

ilastrow = oxlWks.Worksheets("Sheet1").Range("A65536").End(xlUp).Row


this gets the last row with an entry in column a



Can not help you as to why your code fails
Click to expand...

Thanks - that seems faster and more compact. The code crashing still bugs
me! But the goal is to get the thing working.

Thanks again.
 
Ala

The code seems to be crashing a
Worksheets("Sheet1").Range("B" & n

As you are trying to obtain a boolean response the way the code is structured this item does not provide a boolean. The only boolean response is if column A is longer (or equal to) column B. If B is longer than A then that part of the code will evaluate to the contents of B rather than a boolean. Hence the crash

The code should be (using your structure
If Worksheets("Sheet1").Range("B" & n)<> "" Or Worksheets("Sheet1").Range("A" & n) <> "" The

Ton


----- Colleyville Alan wrote: ----

mudraker > said:
Why not do away with your loop and us
Click to expand...

Thanks - that seems faster and more compact. The code crashing still bug
me! But the goal is to get the thing working

Thanks again
 
Back
Top