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

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

RegNo is not defined in your code?

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

HTH

"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

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

Dave Peterson wrote:

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

--

Dave Peterson

Fabulous

MAny Thnaks for your help


"Dave Peterson" wrote:

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

Dave Peterson wrote:

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

--

Dave Peterson