Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbering rows and skipping blanks | Excel Discussion (Misc queries) | |||
Skipping Blanks (Again) | Excel Discussion (Misc queries) | |||
Skipping Blanks | Excel Discussion (Misc queries) | |||
Skipping blanks | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |