You are here:

Excel/VBA: VLookUp issue

Advertisement


Question
Hi Tom,
Using Excel 2007: VBA
I have the following code:
With Workbooks("MasterFile.xlsx.xlsm").Worksheets("Sheet1").Range("s2").Resize(60000, 6)
  .Formula = "=iferror(vlookup($C2,'[NamesFile.xlsx]TempSheet'!$A$2:$g$3000,column()-17,False),"""")"
  .Formula = .Value
End With

The “matchups” are MasterFile Col. C and NamesFile Col A. Both contain text.
The columns B:G that copy over from NamesFile are either blank or contain “X”. They paste to MasterFile cols S:X.
After the code runs, I believe that the “X”’s  are where they should be (I use this for filtering functionality later in the Macro). However, a “0” (zero) shows up in MasterFile cells where there was a blank in NamesFile. This doesn’t hurt the intended filtering functionality, but it sure makes me think there’s something wrong with my code and that maybe the results are not as correct as I think they are.
Why am I getting zeros...something wrong with my code?
Thanks,
Doug

Answer
Doug,

To illustrate what you are observing:

Select two empty/blank cells - say A1 and B1

in A1 put in
=B1

A1 will display a zero instead of appearing blank.  

So your formula is exhibiting this same behavior.  

Now in A1 you could use a formula like this

=if(B1="","",B1)

so within a formula it can actually be determined if the return value is actually a blank and write your formula to account for this.  But if left to its own behavior, excel will return a zero.  So there is nothing wrong with your code.  As written and as you describe the situation, you are getting the expected results.

Yes, vlookup behaves the same as my simple example.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.