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)