Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing "error msg" in VBa
I have a lot of cells returning #N/A! as a result of failed VLOOKUP's.
I would like to change all of these to "blanks", using Range("A1:B70").Copy Range("A1:B70").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'replace "#N/A" with "replword" Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False The first part transforms the erro codes into a value, displayed as #N/A without the exclamationmark from the code. The latter part does absolutely nothing, but leaves the cells as is. When i use this code on any of the cells, Sub t() Dim a As Variant a = ActiveCell.Value Debug.Print a End Sub Ir returns Error 2042. If I try to replace "#N/A" in the replace statement with "Erro 2042" still nothing happens as Error 2042 apparently is not a value. So how do I replace the formulas returning #N/A! with nothing? Jan |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing "IF" or "LOOKUP" formula's with VBA Function? | Excel Programming | |||
Replacing "IF" or "Lookup" formulas with VBA function | Excel Programming | |||
Replacing an ERROR message with "NA" | Excel Discussion (Misc queries) | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |