Problem when hitting an empty cell
 

I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < "" Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not valid".
I have tried formatting that column to text, currency etc. I still hit the
same error.

Anyone have any thoughts on this.
Thanks

Problem when hitting an empty cell
 

Change Value to Text

you should be looping from highest numbered row to lowest numbered row or
you will skip rows. an alternative which would just be an adjustment to
your code is: Also, do you really want to keep deleting the row of the
activecell when the condition is met or to delete the row that meets the
condition?


dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Text < ""
If .ActiveCell.Offset(counter, 22).Text < "" Then
.ActiveCell.Offset(counter,0).EntireRow.Delete()
else
counter = counter + 1
End If
Loop

--
Regards,
Tom Ogilvy


"GPrabaka" wrote in message
...
I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < "" Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not valid".
I have tried formatting that column to text, currency etc. I still hit the
same error.

Anyone have any thoughts on this.
Thanks

Problem when hitting an empty cell
 

Try using the IsEmpty test for data eg.....

Do While Not .ActiveCell.Offset(counter, 0).IsEmpty
If Not .ActiveCell.Offset(counter, 22).IsEmpty Then

I presume you have a With-End With construct around this code, also the
delete entire row may cause a problem.

--
Cheers
Nigel



"GPrabaka" wrote in message
...
I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < "" Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not valid".
I have tried formatting that column to text, currency etc. I still hit the
same error.

Anyone have any thoughts on this.
Thanks

Problem when hitting an empty cell
 

Mr. Ogilvy,
I understand what yu mean about skipping the rows. I will do that. However,
with my original problem of the empty cell, I tried
..ActiveCell.Offset(counter, 22).Text, and it gave me the same error.
Any ides?
Thanks


"Tom Ogilvy" wrote:

Change Value to Text

you should be looping from highest numbered row to lowest numbered row or
you will skip rows. an alternative which would just be an adjustment to
your code is: Also, do you really want to keep deleting the row of the
activecell when the condition is met or to delete the row that meets the
condition?


dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Text < ""
If .ActiveCell.Offset(counter, 22).Text < "" Then
.ActiveCell.Offset(counter,0).EntireRow.Delete()
else
counter = counter + 1
End If
Loop

--
Regards,
Tom Ogilvy


"GPrabaka" wrote in message
...
I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < "" Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not valid".
I have tried formatting that column to text, currency etc. I still hit the
same error.

Anyone have any thoughts on this.
Thanks

Problem when hitting an empty cell
 

Unless you have

With application

or something similar, I wouldn't precede ActiveCell with a period. but,
that is what you originally posted and you said that was working.

In any event, to demonstrate from the immediate window:

my cells had this formula in them
=CHAR(COLUMN()+64)&ROW()

so they displayed there address

In the immediate window:

range("D1").Select
? activeCell.Address
$D$1
Counter = 3
? activecell.Offset(counter,22).Address
$Z$4
? activeCell.Offset(counter,22).Value
Z4
? activeCell.Offset(counter,22).Text
Z4

So text works fine as a substitute for Value. The difference is that it
returns a text string even if the cell contains a number - this avoids the
error you were getting.

--
Regards,
Tom Ogilvy



"GPrabaka" wrote in message
...
Mr. Ogilvy,
I understand what yu mean about skipping the rows. I will do that.
However,
with my original problem of the empty cell, I tried
.ActiveCell.Offset(counter, 22).Text, and it gave me the same error.
Any ides?
Thanks


"Tom Ogilvy" wrote:

Change Value to Text

you should be looping from highest numbered row to lowest numbered row
or
you will skip rows. an alternative which would just be an adjustment to
your code is: Also, do you really want to keep deleting the row of the
activecell when the condition is met or to delete the row that meets the
condition?


dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Text < ""
If .ActiveCell.Offset(counter, 22).Text < "" Then
.ActiveCell.Offset(counter,0).EntireRow.Delete()
else
counter = counter + 1
End If
Loop

--
Regards,
Tom Ogilvy


"GPrabaka" wrote in message
...
I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < ""
Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not
valid".
I have tried formatting that column to text, currency etc. I still hit
the
same error.

Anyone have any thoughts on this.
Thanks

Problem when hitting an empty cell
 

GPrabaka wrote:
I am running into a problem. I have the following code
dim counter as integer
counter = 0
Do While .ActiveCell.Offset(counter, 0).Value < ""
If .ActiveCell.Offset(counter, 22).Value < "" Then
.ActiveCell.EntireRow.Delete()
End If
counter = counter + 1
Loop
If .ActiveCell.Offset(counter,22) is empty, which should be valid, it
returns a fatal error "Cast from String "" to type 'Double' is not valid".
I have tried formatting that column to text, currency etc. I still hit the
same error.

your code seems to be written in VB.NET. if so, try

Do While Not IsNothing(.ActiveCell.Offset(counter, 0).Value)
If Not IsNothing(.ActiveCell.Offset(counter, 22).Value) Then

or

Do While CStr(.ActiveCell.Offset(counter, 0).Value) < ""
If CStr(.ActiveCell.Offset(counter, 22).Value) < "" Then

--
HTH,

okaizawa