Clear cells with #N/A
 

I'm using paste/special to remove vlookup functions which leave #N/A
when no matches were found. I'm not getting any errors but I can't get
the current cell to clear if it has the #N/A. Can anyone assist?
Thanks in advance!

For i = 7 To lastRow
If (CStr(Cells(i, "L").Value) = "#N/A") Then
Cells(i, "L").Value.Clear
End If
Next i


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393138

Clear cells with #N/A
 

On Error Resume Next
Columns(12).SpecialCells(xlFormulas,xlErrors).Clea r
Columns(12).SpecialCells(xlConstants,xlErrors).Cle ar
On Error goto 0

--
Regards,
Tom Ogilvy

"mthomas" wrote in
message ...

I'm using paste/special to remove vlookup functions which leave #N/A
when no matches were found. I'm not getting any errors but I can't get
the current cell to clear if it has the #N/A. Can anyone assist?
Thanks in advance!

For i = 7 To lastRow
If (CStr(Cells(i, "L").Value) = "#N/A") Then
Cells(i, "L").Value.Clear
End If
Next i


--
mthomas
------------------------------------------------------------------------
mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393138

Clear cells with #N/A
 

Try this

For i = 7 To lastRow
If IsError(Cells(i, "L")) Then
Cells(i, "L").Value.Clear
End If
Next i




*** Sent via Developersdex http://www.developersdex.com ***

Clear cells with #N/A
 

Have you tried altering your formula:

=If(Countif(lookupColumn,lookupValue)0,Vlookup(.. ...),"")

this leaves the cell looking like it is blank.
or you can change "" to 0, or .......

--
steveB

Remove "AYN" from email to respond
"mthomas" wrote in
message ...

I'm using paste/special to remove vlookup functions which leave #N/A
when no matches were found. I'm not getting any errors but I can't get
the current cell to clear if it has the #N/A. Can anyone assist?
Thanks in advance!

For i = 7 To lastRow
If (CStr(Cells(i, "L").Value) = "#N/A") Then
Cells(i, "L").Value.Clear
End If
Next i


--
mthomas
------------------------------------------------------------------------
mthomas's Profile:
http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393138

Clear cells with #N/A
 

I appreciate the replies guys, but just an FYI. I decided to use the
replace method for the column and it worked great!

Columns("D:D").Select
Selection.Replace What:="#N/A", Replacement:="", _
SearchOrder:=xlByRows, MatchCase:=False

Appreciate ya and thanks for the help....


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=393138