Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking through entire sheet
I need to look at every cell in the sheet and if it has "#N/A" then I need to
replace it with "" . I know of a long way to do it (looping through each cell in each column), but I wanted to see if anyone new of a shorter way to just look through the whole sheet. So far I have this... and I would do this for each column. I'm using Visual Studio rather than VBA but its similar. Thanks! For i = 1 To lastRow If oApp.Range("B1:B" & lastRow).Value = "#N/A" Then oApp.Range("B" & i).Value = "" End If Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking through entire sheet
Since Excel has Goto/Special/Formulas/Errors, you could record a macro
to get you that far, then test each cell in the selection for "#N/A". - David NewToVB wrote: I need to look at every cell in the sheet and if it has "#N/A" then I need to replace it with "" . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking through entire sheet
Here's some tested VBA code that may work for you:
On Error Resume Next ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select For Each c In Selection If c.Text = "#N/A" Then c.Value = "" Next c - David David Hilberg wrote: Since Excel has Goto/Special/Formulas/Errors, you could record a macro to get you that far, then test each cell in the selection for "#N/A". - David NewToVB wrote: I need to look at every cell in the sheet and if it has "#N/A" then I need to replace it with "" . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit for entire sheet? | Excel Programming | |||
Copy Entire Sheet | Excel Discussion (Misc queries) | |||
Moving An Entire Row From One Sheet To Another | Excel Discussion (Misc queries) | |||
Copy the entire sheet to overlay existing sheet? | New Users to Excel | |||
Subtract entire sheet? | Excel Worksheet Functions |