Open Workbook - Select Range as table for vlookup
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 |
Open Workbook - Select Range as table for vlookup
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 |
Open Workbook - Select Range as table for vlookup
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 |
Open Workbook - Select Range as table for vlookup
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 |
Open Workbook - Select Range as table for vlookup
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 |
All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com