Open Workbook - Select Range as table for vlookup

A

Alan

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
 
D

Dave Peterson

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
 
G

Gary76

RegNo is not defined in your code?

If it is RegNo you are looking for in the first column of PERSONNELRange try
"RegNo"

HTH
 
D

Dave Peterson

Typo...

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 = .Range("C2:D1000") '<-- changed
End With

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

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

End Sub
 
A

Alan

Fabulous

MAny Thnaks for your help


Dave Peterson said:
Typo...

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 = .Range("C2:D1000") '<-- changed
End With

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

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

End Sub
Click to expand...
 
Top