VLOOKUP in VBA

[Robert Lee Wheatley]

Hi all, first posted question.

I think, using VBA to populate a cell with a VLOOKUP string to an
external Workbook, requires the external Workbook to be open at the time
the code is executed.

My code is this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

If ActiveCell.Column <> 16 Then End
UserForm1.Show
If LUG_LOOKUP_INDEX = 10 Then End
r = ActiveCell.Row
c = ActiveCell.Column
TheString = "=VLOOKUP(" & LUG_LOOKUP_INDEX & ", HeaderLugMaterial,
4,TRUE)"
Worksheets("Inputs").Cells(r, c) = TheString
End
End Sub

Each time the cell is populated with the VLOOKUP string the external
Workbook opens and the table_array is highlighted. Not very eloquent.

Looking for validation on my assumption.

Regards,
Bob

 

[Guest]

I would say no, but you need to write the formula as it would look if
refering to a closed workbook

"=Vlookup(searchvalue,'C:\Myfolder\[Myworkbook.xls]Sheet1'!A1:F200,4,False)"