Solved: Supressing '#N/A' in WorksheetFunction.VLookup

[Cosmo]

I am trying to set a range of cells in one worksheet to a lookup value from another worksheet. Everything is working the way I want it, but if there is no matching row in the source worksheet range, then it puts '#N/A' in the cells. I have tried the following 3 options, but haven't come up with a proper solution yet:
[vba]' NOTE - This will leave rows with no matching value as '#N/A'
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
' NOTE - This will leave rows with no matching value blank, but empty cells will now have '0' instead of being empty
rngDestination = WorksheetFunction.IfError(WorksheetFunction.VLookup(rngLookup, rngSource, i, 0), "")
' This doesn't work (Type mismatch)
rngDestination = WorksheetFunction.Substitute(WorksheetFunction.VLookup(rngLookup, rngSource, i, 0), "#N/A", "")
[/vba]
I'm not as familiar with Excel as I am with PowerPoint, so I'm struggling for something that is probably very easy. Can anyone push me in the correct direction on what change I need to make to this line to solve this issue?

BTW, rngDestination, rngLookup and rngSource are all valid ranges, and 'i' is the column I am retrieving. These all work properly.

(Note - this is a followup to a thread I have posted elsewhere: http://www.vbforums.com/showthread.php?t=639064)

[mdmackillop]

You can test to find the error vaslue and handle it accordingly

[vba]

On Error Resume Next
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
MsgBox Err
If Err = 1004 Then
MsgBox "Problem"
Exit Sub
Else
On Error GoTo 0
End If

MsgBox rngDestination


[/vba]

[Cosmo]

You can test to find the error vaslue and handle it accordingly

[vba]

On Error Resume Next
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
MsgBox Err
If Err = 1004 Then
MsgBox "Problem"
Exit Sub
Else
On Error GoTo 0
End If

MsgBox rngDestination


[/vba]

Thanks, but the rngDestination is an entire range spanning multiple rows, not a single value, so I'm not getting an error with this (I'm guessing the error returned is for the last row processed, which has a matching value?).

[Cosmo]

For reference, here's the entirety of the code I am using:
[vba]Option Explicit

Public Function doLookup()
Dim sourceWksht As Worksheet
Dim destWksht As Worksheet

' Column to use as lookup in Destination worksheet (should be first column - XXX_ID)
Dim rngLookup As Range
' Column to set user data in Destination worksheet
Dim rngDestination As Range
' Range of cells in Source worksheet to retrieve user data
Dim rngSource As Range

' First row containing data (Should be row 11 if user made no changes)
Dim startingRow As Integer
' First column containing user data (Should be column 12 if user made no changes)
Dim firstDataColumn As Integer

Dim lastColumn As Integer
Dim lastRow As Integer
Dim i As Integer

' Set source & destination worksheets - These will be function parameters in the final function
Set sourceWksht = Worksheets.Item("TEST_IMPORT")
Set destWksht = Worksheets.Item("TEST")

' Get first row containing data in Destination worksheet
startingRow = 11
firstDataColumn = 12
' Get last row & column in Destination worksheet
lastColumn = FindLastColumn(destWksht)
lastRow = FindLastRow(destWksht)
If (lastColumn < 1) Or (lastRow < 1) Then
' No data in worksheet, exit function
Exit Function
End If

' Set lookup column & destination column in Destination worksheet
If True Then
Set rngLookup = Range(destWksht.Name & "!A" & startingRow).Resize(rowSize:=lastRow - startingRow)
Set rngDestination = Range(destWksht.Name & "!L" & startingRow).Resize(rowSize:=lastRow - startingRow)
Else
'Set rngLookup = Range(destWksht.Name & "!A" & startingRow, Range(destWksht.Name & "!A" & startingRow).End(xlDown))
'Set rngDestination = Range(destWksht.Name & "!L" & startingRow, Range(destWksht.Name & "!L" & startingRow).End(xlDown))
End If

' Get first row containing data in Source worksheet
startingRow = 11
' Get last row & column in Source worksheet
lastColumn = FindLastColumn(sourceWksht) ' Need to get last column of Source worksheet here (Not current)??
lastRow = FindLastRow(sourceWksht) ' Need to get last column of Source worksheet here (Not current)??
If (lastColumn < 1) Or (lastRow < 1) Then
' No data in worksheet, exit function
Exit Function
End If

Application.EnableEvents = False
Application.ScreenUpdating = False

' Set the source range in Source worksheet to locate user data - first column will be the lookup column
Set rngSource = Range(sourceWksht.Name & "!A" & startingRow).Resize(rowSize:=lastRow - startingRow, columnsize:=lastColumn)
For i = firstDataColumn To lastColumn
' Lookup values from current column
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)

' Note need to reset range destination here!
Set rngDestination = rngDestination.Offset(columnoffset:=1)
Next i

Application.EnableEvents = True
Application.ScreenUpdating = True
End Function

Private Function FindLastColumn(Optional ByRef wksht As Worksheet = Nothing) As Integer
'Finds last used column
Dim lastColumn As Integer
Dim NextColumn As Integer
If wksht Is Nothing Then
' If no worksheet/workbook active, need to return 0
If ActiveSheet Is Nothing Then
FindLastColumn = -1
Exit Function
Else
Set wksht = ActiveSheet
End If
End If
'Find last column with text
If WorksheetFunction.CountA(wksht.Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
lastColumn = wksht.Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
FindLastColumn = lastColumn
End Function

Private Function FindLastRow(Optional ByRef wksht As Worksheet = Nothing) As Integer
'Finds last used Row
Dim lastRow As Integer
Dim NextRow As Integer
If wksht Is Nothing Then
' If no worksheet/workbook active, need to return 0
If ActiveSheet Is Nothing Then
FindLastRow = -1
Exit Function
Else
Set wksht = ActiveSheet
End If
End If
'Find last Row with text
If WorksheetFunction.CountA(wksht.Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = wksht.Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
FindLastRow = lastRow
End Function[/vba]

[mdmackillop]

Can you post your workbook?

[mdmackillop]

Or how about
[vba]On Error Resume Next
x = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
MsgBox Err
If Err = 1004 Then
MsgBox "Problem"
Exit Sub
Else
rngDestination = x
On Error GoTo 0
End If
[/vba]

[Cosmo]

Or how about
[vba]On Error Resume Next
x = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
MsgBox Err
If Err = 1004 Then
MsgBox "Problem"
Exit Sub
Else
rngDestination = x
On Error GoTo 0
End If
[/vba]

With that, i'm getting error #91 for each column, and now none of the data gets set.

I did post the code I am using just prior to your last message (http://vbaexpress.com/forum/showpost...stcount=4 ) in case you didn't notice it. Hopefully, you can get an idea of what I am doing. I'm not at all familiar with Excel, and was pretty happy with the progress I have made in short time, but this small detail is practically taking as long as everything else I have worked on for this.

Thanks again for your continued assistance in this.

[mdmackillop]

[vba]Dim x
On Error Resume Next
For i = firstDataColumn To lastColumn
' Lookup values from current column
x = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
If Not Err = 0 Then
x = "Not found"
Err.Clear
End If
rngDestination.Value = x
' Note need to reset range destination here!
Set rngDestination = rngDestination.Offset(columnoffset:=1)
Next i
On Error GoTo 0
[/vba]
A simplified example

[Cosmo]

Thanks again. I downloaded your test file, and found you are setting the rngLookup to a single cell. But I am using a range of cells (from row 11 to the last used row). When I change yours to use a similar range as mine(rows 8-22), I get the '#N/A' again in the cells.

Perhaps since I am not as familiar with Excel, I might not be using this function properly? The code does seem to do what I need it to do as it is, and pretty quickly, aside from this one issue. If I can't make a change to the Lookup line to fix this, can I run another function on the range afterwards to 'cleanup' the cells which have the 'N/A'?

Again, I thank you for your patience and assistance.

[Cosmo]

Aha! [VBA]...
rngDestination.Value = x
Call rngDestination.Replace("#N/A", "")
...[/VBA]

Not sure if this is the cleanest way to solve the problem, but it works for now! If there's a better way, I'd be interested in any other suggestions.

Thanks for your help.

[mdmackillop]

If you can post your workbook I can see where the problem lies. Deleting the N/A values though is simple and effective!

[Cosmo]

If you can post your workbook I can see where the problem lies. Deleting the N/A values though is simple and effective!

Thanks for the offer. I'll have to post it tomorrow though; it's quitting time here, and the workbook contains confidential information, so I'd have to spend a few minutes to do a mockup.

I really appreciate your assistance, I can't thank you enough!

[Cosmo]

If you can post your workbook I can see where the problem lies. Deleting the N/A values though is simple and effective!

Here's a demo. I have stripped this down to the basics that I am using. The yellow area on the 2 worksheets shows the area containing the user data that will be updated with the lookup. There are 2 public subs that show up in the macros list - "Import" and "Import_NO_NA" - run both of these and you will see the results on the first worksheet ("Test"). The first macro will not run the 'replace' line which removes the '#N/A' from the range, the second will.

The rows 11 & 17 have IDs that are not in the second worksheet, so those should show the 'N/A' when the 'Import' is run.

The solution I have does give me the results I want, although in the slim chance that the user has actually entered the value "#N/A" into any of the user-entered cells, it will be stripped out, so I would prefer to remove this when the lookup is run, if possible. Plus, it would probably run faster if it is running one function instead of 2 on the same range.

Thanks again for looking at this.

[mdmackillop]

Small point first: You are missing a row
[vba] ' Set lookup column & destination column in Destination worksheet
Set rngLookup = destWksht.Range("A" & startingRow)
Set rngLookup = rngLookup.Resize(rowSize:=lastRow - startingRow + 1)
Set rngDestination = destWksht.Range("L" & startingRow).Resize(rowSize:=lastRow - startingRow + 1)
[/vba]
Main issue is here
[vba] rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
[/vba]
rngLookup must be a single cell, as in the normal worksheet function.
rngDestination is a block of cells. The same result will be written to each cell in that block. Looping each cell of rngLookup will keep overwriting previous results.

I think this does what you are after (but tell me if I'm wrong!)

I've also included an alternative coding which is more efficient, if I have the result correct.

[Cosmo]

Small point first: You are missing a row
[vba] ' Set lookup column & destination column in Destination worksheet
Set rngLookup = destWksht.Range("A" & startingRow)
Set rngLookup = rngLookup.Resize(rowSize:=lastRow - startingRow + 1)
Set rngDestination = destWksht.Range("L" & startingRow).Resize(rowSize:=lastRow - startingRow + 1)
[/vba]

Thanks for catching that, I thought I had done that (I may have had -1 originally and deleted it)

Main issue is here
[vba] rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
[/vba]
rngLookup must be a single cell, as in the normal worksheet function.
rngDestination is a block of cells. The same result will be written to each cell in that block. Looping each cell of rngLookup will keep overwriting previous results.

This is where I get confused. Once I fixed the areas where I was missing a row (I was also missing the last row in the source range), it does the lookup for the entire range when I run it. I have tested it in 2003 & 2007, and I get the same results in both versions. Is this not the case for you if you run the original code?

If this does work, I would prefer to be able to set the entire range at once, since there will be 10,000-20,000 rows, updating about 35 columns split across 4 different worksheets. I'm assuming looping through every cell across those ranges may take a while longer than what I have now.

I think this does what you are after (but tell me if I'm wrong!)

I've also included an alternative coding which is more efficient, if I have the result correct.

Thanks! I have downloaded your file, and will take a look at it in a little bit.

[mdmackillop]

Finally figured out what is going on!
I think simplest is to clear the error cells
[VBA]
Sub Test()
Dim LkUp As Range
Dim Dta As Range
Dim Res As Range
Set LkUp = Range("lookup")
Set Dta = Range("Data")
Set Res = Range("Output")
Res = Application.WorksheetFunction.VLookup(LkUp, Dta, 2, 0)
Res.SpecialCells(xlCellTypeConstants, 16).ClearContents
End Sub

[/VBA]

[mdmackillop]

For your own code, try
[VBA]For i = firstDataColumn To lastColumn
' Lookup values from current column
' NOTE - This will leave rows with no matching value as '#N/A'
With rngDestination
.Value = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
' Remove '#N/A' from range
.SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
' Note need to reset range destination here!
Set rngDestination = rngDestination.Offset(columnoffset:=1)
Next i[/VBA]

[Cosmo]

For your own code, try
[vba]For i = firstDataColumn To lastColumn
' Lookup values from current column
' NOTE - This will leave rows with no matching value as '#N/A'
With rngDestination
.Value = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
' Remove '#N/A' from range
.SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
' Note need to reset range destination here!
Set rngDestination = rngDestination.Offset(columnoffset:=1)
Next i[/vba]

Thanks again! I'll look into this when I get a chance. I definitely like this much better than my own 'Replace' line if it does what I want, since I won't be hard-coding the '#N/A' into the program. ALthough, I am guessing that it won't resolve the (slim) chance that if a user has actually entered that exact string into the cell manually, it will remove it.

[mdmackillop]

If #N/A is in the destination it will be overwritten by the lookup result. If the source contains #N/A as a string, it will appear in the result, but not if it is a calculated error value.

[Cosmo]

If #N/A is in the destination it will be overwritten by the lookup result. If the source contains #N/A as a string, it will appear in the result, but not if it is a calculated error value.

Awesome, that sounds perfect! I'll test everything out tomorrow.