View Full Version : Solved: Supressing '#N/A' in WorksheetFunction.VLookup
Cosmo
01-25-2011, 12:39 PM
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:
' 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", "")
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
01-25-2011, 12:54 PM
You can test to find the error vaslue and handle it accordingly
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
Cosmo
01-25-2011, 01:05 PM
You can test to find the error vaslue and handle it accordingly
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
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
01-25-2011, 01:08 PM
For reference, here's the entirety of the code I am using:
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
mdmackillop
01-25-2011, 01:10 PM
Can you post your workbook?
mdmackillop
01-25-2011, 01:13 PM
Or how about
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
Cosmo
01-25-2011, 01:20 PM
Or how about
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
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.php?p=234483&postcount=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
01-25-2011, 02:06 PM
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
A simplified example
Cosmo
01-25-2011, 02:34 PM
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
01-25-2011, 02:50 PM
Aha! ...
rngDestination.Value = x
Call rngDestination.Replace("#N/A", "")
...
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
01-25-2011, 03:03 PM
If you can post your workbook I can see where the problem lies. Deleting the N/A values though is simple and effective!
Cosmo
01-25-2011, 03:09 PM
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
01-26-2011, 07:47 AM
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
01-26-2011, 04:09 PM
Small point first: You are missing a row
' 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)
Main issue is here
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
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
01-27-2011, 07:57 AM
Small point first: You are missing a row
' 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)
Thanks for catching that, I thought I had done that (I may have had -1 originally and deleted it)
Main issue is here
rngDestination = WorksheetFunction.VLookup(rngLookup, rngSource, i, 0)
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
01-27-2011, 10:26 AM
Finally figured out what is going on!
I think simplest is to clear the error cells
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
mdmackillop
01-27-2011, 12:34 PM
For your own code, try
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
Cosmo
01-27-2011, 03:18 PM
For your own code, try
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
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
01-27-2011, 03:35 PM
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
01-27-2011, 03:40 PM
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.
Cosmo
01-31-2011, 11:34 AM
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.
I haven't had the opportunity to look this over completely, but from a quick check, it looks to be exactly what I need. Thank you very much!:bow:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.