VBA VLOOKUP to external workbook... help

[Chrisjd2]

Hi Gents,

Could you help me, I need to write VBA code to do a vlookup in a cells to an external spreadsheet that is on my desktop. I dont know where to start with the code?

It needs to be in the macro as it needs to loop through 10,000 lines.

Cheers
Chris

 

[GJSBED]

If you record yourself creating the vlookup with the macro recorder it should be fine, set a range for the lookup though that accounts for the external sheets list getting bigger a some point in the unknown future, plan ahead!

Then stick it into the loop.

 

[Chrisjd2]

If you record yourself creating the vlookup with the macro recorder it should be fine, set a range for the lookup though that accounts for the external sheets list getting bigger a some point in the unknown future, plan ahead!

Then stick it into the loop.

I've tried that, unfortunately the recorder puts the result of the worksheet function "=Vlookup... ETC" in each sell as a string. So it is not actually putting the result of the vlookup in.

 

[GJSBED]

The cell your putting it in might be set to TEXT, in which case it won't accept any formula, change the format of all cells you want the lookup in to GENERAL

Range("A1").Select   

 Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(wahtever your reference)"