You have another thread going. Why start this one?



Rbp9ad wrote:

Dave Peterson provided me with the following code that changes the lookup
table in the vlookup function. The tables are on separate sheets of the same
workbook and named the month name and 1(i.e. January1).

Option Explicit
Function SpecLookup(VRN As Variant) As Variant

Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

For iCtr = 1 To 12
Set testRng = Nothing
On Error Resume Next
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1), "mmmm")
& "1").RefersToRange
On Error GoTo 0
res = Application.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
'Naming Error--just skip?
Else
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

This works great in the workbook that I developed to test the function. What
I want is one that opens the file that I want to lookup the values from. To
this end I adapted the following code.

Option Explicit
Function SpecLookup(VRN As Variant) As Variant

Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

Workbooks.Open Filename:="F/Receiving Report Log/Receiving Report Log
2005.xls"

For iCtr = 1 To 12
Set testRng = Nothing
On Error Resume Next
Set testRng = Workbooks("Receiving Report Log
2005.xls").Names(Format(DateSerial(2005, iCtr, 1), "mmmm") &
"1").RefersToRange
On Error GoTo 0
res = Application.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
'Naming Error--just skip?
Else
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

--

Dave Peterson