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

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

But you do have at least one typo in this line:

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

Maybe...

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

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

ps. If you're going to use this function in a worksheet cell, then you won't be
able to open that workbook in code.

And if you're going to use this in your code, then I would think you'd want to
open the workbook once, and call the routine multiple times.

=====
If I recall correctly, you were going to use the function from a worksheet cell.

Why not just open that report log once--you could open it when your workbook
opens.

sub auto_open()
Workbooks.Open _
Filename:="F:\Receiving Report Log\Receiving Report Log 2005.xls"
end sub

And never have to worry about it.

Dave Peterson wrote:

But you do have at least one typo in this line:

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

Maybe...

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

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

--

Dave Peterson

Let me re-formulate the problem regarding setting the minimum no. of
sheets in a Workbook. (The Maximum flip-side is tractable and solved).
If we desire a minimum of N sheets:

1. Insertions may be allowed if Worksheets count is *equal
to/greater *
than N;

2. While deletions can be done for as long as sheet count is
*greater than
* N.

3. When sheet count is exactly N, *no fresh insertions * should be

allowed. (This is the crux of the problem).

4. But should the sheet count happen to be less than N, for a
start, insertions
could be permitted.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=474337

Sorry, the above post of mine was misdirected to this thread. I have
since relocated it to its rightful thread (titled: How do I limit the
number of sheets in a Workbook").

David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=474337