#N/A values 3

[mal2ty]

I'm working with VBA to replace #N/A values with 0s in an Excel spreadsheet.

The #N/A values in my spreadsheet are the result of the VLOOKUP function.

The problem I'm encountering is that I receive an error every time I try to check for a #N/A value. Currently, I'm trying to detect these values by using the following code:

If (Selection.Offset(r, c).Value = "#N/A&quot Then

Is there a way to do this without receiving errors?

 

[mal2ty]

Amazingly enough, I believe that I've found a solution to my own problem. For those interested, the code I'm now using is as follows:

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Cells.Replace What:="#N/A", Replacement:="0", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

 

[Chance1234]

ISNA !!!


IF(ISNA(r,c)) then

 

[Goska]

Thanks Chance. I was looking for that too. Works as a formula in a sheet too.

 

[mal2ty]

Is there a reference that needs to be added to use the IsNA function? When I tried to use it, I received the following error:

Compile error:
Sub or Function not defined

 

[Goska]

try this

dim SomeVal as INT/STRING/VARAINT
SomeVal=Cells(R,C)

If(ISNA(SomeVal))then

I haven't tested this, but it should be a solution for you error

 

[mal2ty]

Goska:

I tried your proposed solution, but ended up with the same error. Here is the code I was using:

Dim sngGetValue As Single
sngGetValue = Selection.Offset(intRow + 5, CStr(intCol + 64)).Value

If (IsNA(sngGetValue)) Then
sngGetValue = 0
End If

Any other ideas?

 

[mal2ty]

Here's something that seems to work. It's pretty crude coding, but at least it seems to get the job done.

Dim intRow As Integer
Dim intCol As Integer
Dim sngGetValue As Single
Dim strCellAddress As String

Selection.Offset(intRow, intCol + 7).Value = "=CELL(" & """" & "address" & """" & &quot"
strCellAddress = Selection.Offset(intRow, intCol + 7).Value

Selection.Offset(intRow, intCol + 6).Value = "=IF(ISNA(OFFSET(" & strCellAddress & ",0,1,1,1)),0,OFFSET(" & strCellAddress & ",0,1,1,1))"

sngGetValue = Selection.Offset(intRow, intCol + 6).Value
Selection.Offset(intRow, intCol + 6).Value = ""
Selection.Offset(intRow, intCol + 7).Value = ""

 

[kylegjordan]

if the vlookup formula is in the actual cell then you can change that formula to read:

=if(vlookup(arguments)="#N/A",0)
no need for the "otherwise" comma

 

[mal2ty]

Using the proposed formula resulted in only #N/A and FALSE values. This is how I tested it:

=IF(VLOOKUP(B10,'ExcelFile.xls!$A:$D,4,FALSE)="#N/A",0)

However, I was able to get the following formula to provide the desired results:

=IF(ISNA(VLOOKUP(B12,'ExcelFile.xls'!$A:$D,4,FALSE)),0)

 

[Store300]

Try =If(IsError(Vlookup(XX)),"0",Vlookup(XX)) Store300

Store300@ftnetwork.com

 

[bryanbayfield]

mal2ty, I think the reason you were getting "Sub or function not defined" is because ISNA is an Excel worksheet function, with no VB counterpart.

Try this:

If (Application.WorksheetFunctions.IsNA(sngGetValue)) Then
sngGetValue = 0
End If

in the ocde you posted above.

Bryan.