Excel/VLookUp problem

Advertisement


Question
QUESTION: Hi Tom,
Using Excel 2007 VBA
I've got this bit of code:

   Dim lastrowPrc As Long
   lastrowPrc = Workbooks("Master.xlsx.xlsm").Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

   With Workbooks("Master.xlsx.xlsm").Worksheets("Sheet1").Range("f2").Resize(lastrowPrc, 8)
     .Formula = "=iferror(vlookup($A2,'[PriceFile.xlsx]Prices'!$A$2:$L$3000,column()-1,False),"""")"
     .Formula = .Value
   End With

(Sorry, copy/paste messes up alignment and line breaks)
It works OK, except that all cells get erased in Master.xlsx if there is no match from PriceFile.xlsx.
Can this be tweaked so that the matching rows copy over OK, but if there is no match it just leaves the row unchanged?

Thanks!

ANSWER:
Doug,
you formula says if it encounters an error to display a null string ""

when you then do the .formula = .value, the cell is filled with a null string.  You will find the cell isn't empty because it has a null string in it.  Try selecting the column and do goto constants and choose text.  

To do what you describe, you would need to loop through your cells and process each on one at a time.  You would execute the formula in VBA (not put it in the cell), then test if you received a valid result.  If you do, then place it in the cell and move to the next cell.  If not, then move to the next cell.

So that is the way I would do it.

An alternative would be to put all the values from the cells where the formula would be entered and put them in an array.  Then after the formula is applied and replaced, then you would loop through the cells and where you find the null string, put in the value from the array.  

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom,
The way you would do it seems to make sense to me, and would replace contents where appropriate, otherwise leave them alone.
My problem is I don't know how to do that.

Answer
Doug,

I built some test file and ran you macro and my macro.  My macro produced the same as yours except in rows that there was no match - in those rows, it did not change the existing values.

Since it is looping, it will probably be slower, but shouldn't be too bad.

Sub ABC()
Dim bk1 As Workbook, bk2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, cell As Range, r2 As Range, r2A As Range
Dim lastrowPrc As Long
Set bk1 = Workbooks("Master.xlsx.xlsm")
Set bk2 = Workbooks("PriceFile.xlsx")
Set sh1 = bk1.Worksheets("Sheet1")
Set sh2 = bk2.Worksheets("Prices")
Set r2 = sh2.Range("A2:L3000")
Set r2A = r2.Resize(, 1) ' Just column A of the Price Sheet Range
lastrowPrc = sh1.Cells(Rows.Count, "A").End(xlUp).Row
Set r1 = sh1.Range("F2").Resize(lastrowPrc, 1)
For Each cell In r1
  If Application.CountIf(r2A, sh1.Cells(cell.Row, "A")) > 0 Then
   With cell.Resize(1, 8)
    .Formula = "=iferror(vlookup($A" & cell.Row & _
      ",'[PriceFile.xlsx]Prices'!$A$2:$L$3000,column()-1,False),"""")"
    .Formula = .Value
   End With
  End If
Next
End Sub

I used and initialized a lot of variables - thus so many lines of code.  I could have used much fewer and had fewer lines of code. For example instead of

Set bk2 = Workbooks("PriceFile.xlsx")
Set sh2 = bk2.Worksheets("Prices")
Set r2 = sh2.Range("A2:L3000")

I could have just done
set r2 = Workbooks("PriceFile.xlsx").Worksheets("Prices").Range("A2:L3000")

as an 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.