Loop while skipping blanks
 

How do I do the following?

I want to convert text to a number but skip the blanks in a range

this is what I have, but it debugs because I have blanks

Range("C2").select
Do
activecell.value = clng(activecell.value)
activecell.offset(1,0).select
Loop Until activecell.row = 500

How do I skip the empty or blank cells but still loop the code to cel
500.

Thank you for any help

Jos

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=52498

Loop while skipping blanks
 

try:

For Each cell In Range("C2:C500")
If cell.Value < "" Then cell.Value = CLng(cell.Value)
Next

(Xl2003: blanks give value of 0)

HTH

"jhahes" wrote:


How do I do the following?

I want to convert text to a number but skip the blanks in a range

this is what I have, but it debugs because I have blanks

Range("C2").select
Do
activecell.value = clng(activecell.value)
activecell.offset(1,0).select
Loop Until activecell.row = 500

How do I skip the empty or blank cells but still loop the code to cell
500.

Thank you for any help

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=524984

Loop while skipping blanks
 

dim x as int

x=0
with Range("C2")
Do
if len( .offset(x,0))0 then .offset(x,0).value = clng(.offset(x,0))
x=x+1
Loop Until x=499

end with



"jhahes" wrote:


How do I do the following?

I want to convert text to a number but skip the blanks in a range

this is what I have, but it debugs because I have blanks

Range("C2").select
Do
activecell.value = clng(activecell.value)
activecell.offset(1,0).select
Loop Until activecell.row = 500

How do I skip the empty or blank cells but still loop the code to cell
500.

Thank you for any help

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=524984

Loop while skipping blanks
 

Hi Toppers, I tried your solution and it made me debug. It state
variable not defined with the word cell highlighted in yellow.

Duke, I also tried your solution and it gave me a debug error of typ
mismatch with .offset(x,0).value highlighted in yellow.


Can you please help...


Thanks...

Jos

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=52498

Loop while skipping blanks
 

Post your code OR if you have set Option Explicit then add

Dim cell as range

to the macro.

"jhahes" wrote:


Hi Toppers, I tried your solution and it made me debug. It stated
variable not defined with the word cell highlighted in yellow.

Duke, I also tried your solution and it gave me a debug error of type
mismatch with .offset(x,0).value highlighted in yellow.


Can you please help...


Thanks...

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=524984

Loop while skipping blanks
 

Toppers,

I put in the dim cell as range

and I am getting Run-time error '13': Type Mismatch


Here is my complete code

If Sheet2.Visible = xlSheetHidden Then
Sheet2.Visible = xlSheetVisible
End If
Sheet2.Activate
Range("A2").Select
Do
ActiveCell.Value = CLng(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""


Dim cell As Range
For Each cell In Range("C2:C500")
If cell.Value < "" Then cell.Value = CLng(cell.Value)
Next


Thanks for any help.....

Jos

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=52498

Loop while skipping blanks
 

Your code worked OK for me but I am confused. In your original posting you
were looping through column C for 500 rows... now you are looping through
column A. So my code appears to be redundant. Any errors (Type mismatch) are
probably due to the data in column C (or A)i.e. non-numeric data which cannot
be converted to LONG.

"jhahes" wrote:


Toppers,

I put in the dim cell as range

and I am getting Run-time error '13': Type Mismatch


Here is my complete code

If Sheet2.Visible = xlSheetHidden Then
Sheet2.Visible = xlSheetVisible
End If
Sheet2.Activate
Range("A2").Select
Do
ActiveCell.Value = CLng(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""


Dim cell As Range
For Each cell In Range("C2:C500")
If cell.Value < "" Then cell.Value = CLng(cell.Value)
Next


Thanks for any help.....

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=524984