If the workbook you're looking up in is closed, as your formula seems to
indicate, then I think Dave's approach is the way to go. With an open
workbook you can also do the lookup without using a formula on a worksheet.
This illustrates two ways to do it although you'll have to adapt them to
your specific situation:

Sub a()
Dim WB As String
Dim WS As String
Dim Rg As String
Dim Result As Variant
Dim Val2Lookup As Variant
WB = "book1.xls"
WS = "Sheet1"
Rg = "A1:B3"
Val2Lookup = "b"
Result = Application.VLookup(Val2Lookup, _
Workbooks(WB).Worksheets(WS).Range(Rg), _
2, False)
End Sub

Sub aa()
Dim Result As Variant
Dim Val2Lookup As Variant
Val2Lookup = "b"
Result = Application.Evaluate("VLOOKUP(""" & Val2Lookup _
& """,[Book1.xls]Sheet1!$A$1:$B$3,2,FALSE)")
End Sub

Btw, the formula you posted is really inefficient:

VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)

You reference a range with 35 columns when all you need is 3 since your
offset is 3:

VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$C,3,FALSE)),"",


--
Jim Rech
Excel MVP
"R D S" wrote in message
...
|
=IF(ISNA(VLOOKUP($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE)),"",VLOOKU
| P($B$1,'E:\[RxeyeBatch.xls]Sheet1'!$A:$AI,3,FALSE))
|
| OK....
| the above formula works on the sheet.
| i am trying to implement the above into a userform and instead of looking
at
| cell B1 the lookup value is data entered into a txtbox named 'reference'
and
| the value needs to be returned to the next textbox in the userform which
is
| named 'name'.
|
| Thanks,
| Rick
|
|
| Jim Rech wrote:
||| With such a generic question a specific answer isn't possible. But
||| in general if you can write a formula in a cell that returns the
||| result you want, yes, you can do it from VB.
|||
||| --
||| Jim Rech
||| Excel MVP
||| "R D S" wrote in message
||| ...
|||| Hi,
|||| If I enter a number into a userform in a box called 'reference' can
|||| I then use a vlookup to fill in the next box 'name' from an
|||| external file?
|||| I can use the vlookup on a sheet but am not sure of the syntax?
|||| within VBa.
||||
|||| TIA,
|||| Rick
|
|
|
|