Excel/VBA: VLookUp issue
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
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?
To illustrate what you are observing:
Select two empty/blank cells - say A1 and B1
in A1 put in
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
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.
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