Clear cells with #N/A

[mthomas]

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

[Tom Ogilvy]

On Error Resume Next
Columns(12).SpecialCells(xlFormulas,xlErrors).Clear
Columns(12).SpecialCells(xlConstants,xlErrors).Clear
On Error goto 0

--
Regards,
Tom Ogilvy

"mthomas" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Edward Ulle]

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 ***

[STEVE BELL]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[mthomas]

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....