Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unwanted values
I have the following code:
Worksheets("Auto_Working").Range("A2:A11").Value = ListBox2.List ListBox2 contains 2237, 2388 It produces: A 1 Employees 2 2237 3 2388 4 #N/A 5 #N/A 6 #N/A 7 #N/A 8 #N/A 9 #N/A 10 #N/A 11 #N/A When it gets to the #N/A processing data, it has to be manualy stopped. Please help, I have tried the following to remove them. For Each Cell In Range("A2:A11") If LCase(Cell.Value) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell This caused a error, and could not continue or find the #N/A. It stopped in it's tracks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unwanted values
I would be inclined to rewrite teh formulas to avoid the #NA's but that being
said this code should work for you... For Each Cell In Range("A2:A11") If UCase(Cell.Text) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell Note the Cell.Text to return the text of the cell and not the value and the use of UCase not LCase... -- HTH... Jim Thomlinson "trward79" wrote: I have the following code: Worksheets("Auto_Working").Range("A2:A11").Value = ListBox2.List ListBox2 contains 2237, 2388 It produces: A 1 Employees 2 2237 3 2388 4 #N/A 5 #N/A 6 #N/A 7 #N/A 8 #N/A 9 #N/A 10 #N/A 11 #N/A When it gets to the #N/A processing data, it has to be manualy stopped. Please help, I have tried the following to remove them. For Each Cell In Range("A2:A11") If LCase(Cell.Value) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell This caused a error, and could not continue or find the #N/A. It stopped in it's tracks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unwanted values
How would you sugest that, that would seem cleaner, as there are 1 to 10
employees, possibly more that could be used here. How can I re-write to make the #N/A not be there? "Jim Thomlinson" wrote: I would be inclined to rewrite teh formulas to avoid the #NA's but that being said this code should work for you... For Each Cell In Range("A2:A11") If UCase(Cell.Text) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell Note the Cell.Text to return the text of the cell and not the value and the use of UCase not LCase... -- HTH... Jim Thomlinson "trward79" wrote: I have the following code: Worksheets("Auto_Working").Range("A2:A11").Value = ListBox2.List ListBox2 contains 2237, 2388 It produces: A 1 Employees 2 2237 3 2388 4 #N/A 5 #N/A 6 #N/A 7 #N/A 8 #N/A 9 #N/A 10 #N/A 11 #N/A When it gets to the #N/A processing data, it has to be manualy stopped. Please help, I have tried the following to remove them. For Each Cell In Range("A2:A11") If LCase(Cell.Value) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell This caused a error, and could not continue or find the #N/A. It stopped in it's tracks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unwanted values
Depends on the formula you are using in your cells. I will assume that you
are using a Vlookup. If so then you can do a number of things. My preference is to use a countif (count the number of matching values before you try to match). =if(countif($C$1:$C$10, B1)=0, "", vlookup(B1, $C$1:$C$10, 2, false)) There is also an ISNA function that you could use to determine if the function is returning NA =if(isna(ThisFunction), "", ThisFunction) -- HTH... Jim Thomlinson "trward79" wrote: How would you sugest that, that would seem cleaner, as there are 1 to 10 employees, possibly more that could be used here. How can I re-write to make the #N/A not be there? "Jim Thomlinson" wrote: I would be inclined to rewrite teh formulas to avoid the #NA's but that being said this code should work for you... For Each Cell In Range("A2:A11") If UCase(Cell.Text) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell Note the Cell.Text to return the text of the cell and not the value and the use of UCase not LCase... -- HTH... Jim Thomlinson "trward79" wrote: I have the following code: Worksheets("Auto_Working").Range("A2:A11").Value = ListBox2.List ListBox2 contains 2237, 2388 It produces: A 1 Employees 2 2237 3 2388 4 #N/A 5 #N/A 6 #N/A 7 #N/A 8 #N/A 9 #N/A 10 #N/A 11 #N/A When it gets to the #N/A processing data, it has to be manualy stopped. Please help, I have tried the following to remove them. For Each Cell In Range("A2:A11") If LCase(Cell.Value) = "#N/A" Then Cell.Resize(1, 5).ClearContents End If Next Cell This caused a error, and could not continue or find the #N/A. It stopped in it's tracks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove or disable unwanted fonts | Excel Discussion (Misc queries) | |||
how do i remove unwanted AutoRecover exceptions? | Excel Worksheet Functions | |||
remove unwanted spaces? | Excel Worksheet Functions | |||
remove unwanted text after the 7th position | Excel Discussion (Misc queries) | |||
Remove unwanted tabs | Excel Programming |