Vlookup in VBA giving me grief

The following code returns a #VALUE!
The Vlookup will work properly with the in cell function
The VBA version of Vlookup requires some text changes before it's accepted

Can you provide the correct coding for me please?
Thanks
-Colin

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

'this is the formula that works in cell =VLOOKUP(6999,'C:\Users\ccoady\Desktop\Tenant Billing\QuoteListing.xlsx'!QuoteNumber,1,TRUE)

Two things:

"C:\....!quotenumber" in the VBA statement is just a text string. In the Excel formula, Excel recognizes it as a reference to a range (in a closed workbook, I assume). You need to give the VBA version of the lookup function a proper range object for that argument, not a text string.

Which leads to the second thing. VBA really does not like working with ranges in closed workbooks. It cannot go to a closed workbook and extract the information from that named range. If you are willing to open the workbook, you could use something like Workbooks("QuoteListing.xlsx").Range("QuoteNumber") (or whatever the syntax needs to be) as the range object for that VLOOKUP() argument. Called from the worksheet cell, Excel does not have the same limitation. It may take longer, but Excel knows how to extract a range object from a closed workbook, but VBA does not.

VBA code using Application.Vlookup can't use an external file reference the same way you can in a worksheet. The second argument has to have a Range or array type. What I would suggest is to use VBA to put the formula in the cell, then convert to a value.

You do not need to use a variable to do this, and you do not need to Select the cell.

Please Login or Register  to view this content.