Removing #N/A

[shiftyspina]

Hi All

I got this code from browsing around the web looking for an answer.....

Private Sub CommandButton2_Click()

Dim rng As Range, cell As Range, fmla As String
If TypeName(Selection) <> "Range" Then
MsgBox "Select one or more worksheet cells."
Exit Sub
End If
On Error Resume Next
If Selection.Cells.Count = 1 Then
If Selection = CVErr(x1ErrNA) Then
fmla = Right(Selection.Formula, Len(Selection.Formula) - 1)
Selection.Formula = "=if(isna(" & fmla & "), """"," & fmla & ")"
End If
GoTo e
End If
Set rng = Selection.SpecialCells(x1celltypeformulas, 16)
If rng Is Nothing Then GoTo e
For Each cell In rng
If cell = CVErr(x1ErrNA) Then
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=if(isna(" & fmla & "), """"," & fmla & ")"
End If
Next
e:
On Error GoTo 0


End Sub

What i want is for the code to remove any #N/A cells in my sheet, i have used a index and match formula to look up values for me, and it returns a lot of #N/As but because this sheet can change at any time i dont want to delete out the #N/As i want the formula to stay in the cells, but cant build in an isna or iserror formula with the index and match formula.

Basically i want this code to turn any #N/As in the worksheet to 0 or - but only by clicking the command button?

Would anyone know how this can be done?

Dan

[AB33]

Use Iferror function with your formula

[shiftyspina]

ok il give that a go thanks

[AB33]

May also be

Private Sub CommandButton2_Click()

Dim rng As Range, cell As Range, fmla As String
If TypeName(Selection) <> "Range" Then
MsgBox "Select one or more worksheet cells."
Exit Sub
End If
On Error Resume Next
If Selection.Cells.Count = 1 Then
If IsError(Selection) Then
fmla = Right(Selection.Formula, Len(Selection.Formula) - 1)
Selection.Formula = "=if(isna(" & fmla & "), """"," & fmla & ")"
End If
GoTo e
End If
Set rng = Selection.SpecialCells(x1celltypeformulas, 16)
If rng Is Nothing Then GoTo e
For Each cell In rng
If IsError(cell) Then
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=if(isna(" & fmla & "), """"," & fmla & ")"
End If
Next
e:
On Error GoTo 0


End Sub

[jevelvel]

Hi,

Can you help me with how to use remove n/a to my vlookup formula?

My formula is =VLOOKUP(C100,'MCS Agents list'!$D$5:$G$23,2,1)....

pleeeeeeese!!!!!!!!!!!!!!!

[FDibbins]

Hi jevelvel and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[shiftyspina]

Try.....

=(iferror(vlookup(c100,'MCS Agents List'$D$5:$G$23,2,1)),0)

Or

=(iferror(vlookup(c100,'MCS Agents List'$D$5:$G$23,2,1)),"")

[jevelvel]

thanks!

[FDibbins]

shiftyspina...
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc