About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Named range links to external workbook, help!	WitchMaster	Charts and Charting in Excel	2	October 11th 05 12:07 PM
Lookup Table Dilemma	Karen	Excel Worksheet Functions	2	June 10th 05 08:22 PM
Use Sheet CodeNames to Select Sheet in Different Workbook	Randy	Excel Discussion (Misc queries)	1	June 10th 05 12:17 AM
Pivot Table - max rows allowed in data range	dmotika	Excel Discussion (Misc queries)	2	May 26th 05 05:52 PM
Stubborn toolbars in Excel	007	Excel Discussion (Misc queries)	9	December 11th 04 02:02 PM

Menu

About Us