VLOOKUP in VBA
 

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

*** Sent via Developersdex http://www.developersdex.com ***

VLOOKUP in VBA
 

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)"

--
Regards,
Tom Ogilvy


"Robert Lee Wheatley" wrote:


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

*** Sent via Developersdex http://www.developersdex.com ***