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

An error value is a special type of Variant, not the string "#N/A" (or
one of the other error values). You can't do a Replace operation with
an error type as the Find value, so you'll need to use a loop to find
the #N/A cells. To get an error type of test against the cell values,
you use the CVErr function to convert the error number to an error
type variant. For example,

Dim R As Range
For Each R In Range("A1:F10").SpecialCells(xlCellTypeFormulas)
If R.Value = CVErr(xlErrNA) Then
R.Value = "replaced"
End If
Next R

This will replace all the #N/A errors in cells with a formula with the
string "replaced". Change "replaced" to whatever you want to replace
the #N/A values with.

An Error 2042 is the representation of an #N/A error. The constant
value xlErrNA is a Long type value equal to the number 2042. When
that number is passed to CVErr, CVErr returns the corresponding Error
Type variable, an #N/A error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 3 Nov 2009 16:31:56 +0100, "Jan Kronsell"
wrote:

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

Try this:
Sub dl()
Dim c As Range
For Each c In Range("A1:C5") 'Change to actual
If IsError(c.Value) Then
c = ""
End If
Next
End Sub



"Jan Kronsell" wrote in message
...
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

Try recording a macro when you:

Select the range
Edit|goto special (or F5 or ctrl-g)
Special
Formulas
Uncheck Numbers, text, logicals, but keep Errors checked.

Then hit the delete key on the keyboard.

Stop recording

This will clean those #n/a's along with #ref!'s, div/0, ..., well, all those
errors!

Jan Kronsell wrote:

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

--

Dave Peterson

You could use the .text property instead of the .value property:

Sub t()
Dim a As String
a = ActiveCell.Text
Debug.Print a
End Sub


Jan Kronsell wrote:

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

--

Dave Peterson

Thank you all. I used Chips method and it worked great.

Jan

Chip Pearson wrote:
An error value is a special type of Variant, not the string "#N/A" (or
one of the other error values). You can't do a Replace operation with
an error type as the Find value, so you'll need to use a loop to find
the #N/A cells. To get an error type of test against the cell values,
you use the CVErr function to convert the error number to an error
type variant. For example,

Dim R As Range
For Each R In Range("A1:F10").SpecialCells(xlCellTypeFormulas)
If R.Value = CVErr(xlErrNA) Then
R.Value = "replaced"
End If
Next R

This will replace all the #N/A errors in cells with a formula with the
string "replaced". Change "replaced" to whatever you want to replace
the #N/A values with.

An Error 2042 is the representation of an #N/A error. The constant
value xlErrNA is a Long type value equal to the number 2042. When
that number is passed to CVErr, CVErr returns the corresponding Error
Type variable, an #N/A error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 3 Nov 2009 16:31:56 +0100, "Jan Kronsell"
wrote:

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