I dropped some of the .Select's and added some declarations:

Option Explicit
Sub testme()

Dim OFFICE As Workbook
Dim Result As String
Dim PERSONNELRange As Variant 'it can return an error
Dim RegNo As Variant

RegNo = "asdf"

Set OFFICE = Workbooks.Open("c:\OFFICE.xls")
With OFFICE.Worksheets("Personnel")
Set PERSONNELRange = Worksheets("Personnel").Range("C2:D1000")
End With

Result = Application.VLookup(RegNo, PERSONNELRange, 2, False)

If IsError(Result) Then
MsgBox "Not found"
Else
MsgBox Result, vbOKOnly
End If

End Sub



Alan wrote:

Looking to write some code that will open a workbook/worksheet and then take
a range and use it as a table for a vlookup query ...

HAve entered the following

Dim OFFICE As Workbook
Dim Result As String
Dim PERSONNELRange As Range

Workbooks.Open ("c:\OFFICE.xls")
Worksheets("Personnel").Select
PERSONNELRange = Worksheets("Personnel").Range("C2", "D1000").Select
Result = Application.WorksheetFunction.VLookup(RegNo, PERSONNELRange, 2,
False)

msg = MsgBox(Result, vbOKOnly)

Not working at all ... can anyone assist with a step for a hint please
Many Thanks

--

Dave Peterson