I am trying to write a function that loops so that I can use vlookup in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month name
followed by 1(i.e. January1). The loop should go to the next range when it
does not find the value, but currently it does not. It will only calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant


iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1), "mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

Just curious. What was wrong with the original suggestion?

Rbp9ad wrote:

I am trying to write a function that loops so that I can use vlookup in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month name
followed by 1(i.e. January1). The loop should go to the next range when it
does not find the value, but currently it does not. It will only calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1), "mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

--

Dave Peterson

I got a #VALUE error when I put in the following code. I copied this
straight from the module in which I had it. This is only the second custom
function I wrote so there is probably something that I missed. Any help
would be rgeatly appreciated.

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.WorksheetFunction.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
MsgBox ("Naming Error--just skip?")
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

"Dave Peterson" wrote in message
...
Just curious. What was wrong with the original suggestion?

Rbp9ad wrote:

I am trying to write a function that loops so that I can use vlookup in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month name
followed by 1(i.e. January1). The loop should go to the next range when
it
does not find the value, but currently it does not. It will only
calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3,
False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

--

Dave Peterson

Hmmmmmmm.

You changed the code....

Option Explicit
Function SpecLookup(VRN As Variant) As Variant

Application.Volatile True

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
'MsgBox "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

#1. You dropped an Else line right (added back under the msgbox line. In fact,
I wouldn't use a msgbox except for debugging. You don't want this to show up
each time the workbook recalculates.

#2. You dropped the "application.volatile true" line. Since you don't pass all
the ranges that the UDF depends on, if you can a cell value in (say) the
December1 range, your function won't recalculate.

In fact, I'd recalculate before trusting any of this output--this UDF could be
one calculatation behind.

#3. You added .worksheetfunction to this:

res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)

There is a significant difference on how excel handles error when you use:
Application.WorksheetFunction.VLookup
compared to
application.vlookup

One causes a trappable error that the code has to catch. The other (easier to
use in my opinion) returns an error you can check.

I used application.vlookup and then checked for that error.

When you changed it to Application.WorksheetFunction.VLookup, then the code just
caused an error and stopped right there.

Rbp9ad wrote:

I got a #VALUE error when I put in the following code. I copied this
straight from the module in which I had it. This is only the second custom
function I wrote so there is probably something that I missed. Any help
would be rgeatly appreciated.

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.WorksheetFunction.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
MsgBox ("Naming Error--just skip?")
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

"Dave Peterson" wrote in message
...
Just curious. What was wrong with the original suggestion?

Rbp9ad wrote:

I am trying to write a function that loops so that I can use vlookup in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month name
followed by 1(i.e. January1). The loop should go to the next range when
it
does not find the value, but currently it does not. It will only
calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3,
False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

--

Dave Peterson

--

Dave Peterson

This worked great. I am still new at this so when I started typing
Application.Vlookup and it did not pop out of the library I just assumed
that I needed to add worksheet function to it. I changed the function to
Application.Vlookup and now both RRLookup and SpecLookup work thanks so much
for your help. I did not know that when a worksheet function returns an
error value that the code will stop.

"Dave Peterson" wrote in message
...
Hmmmmmmm.

You changed the code....

Option Explicit
Function SpecLookup(VRN As Variant) As Variant

Application.Volatile True

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
'MsgBox "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

#1. You dropped an Else line right (added back under the msgbox line. In
fact,
I wouldn't use a msgbox except for debugging. You don't want this to show
up
each time the workbook recalculates.

#2. You dropped the "application.volatile true" line. Since you don't
pass all
the ranges that the UDF depends on, if you can a cell value in (say) the
December1 range, your function won't recalculate.

In fact, I'd recalculate before trusting any of this output--this UDF
could be
one calculatation behind.

#3. You added .worksheetfunction to this:

res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)

There is a significant difference on how excel handles error when you use:
Application.WorksheetFunction.VLookup
compared to
application.vlookup

One causes a trappable error that the code has to catch. The other
(easier to
use in my opinion) returns an error you can check.

I used application.vlookup and then checked for that error.

When you changed it to Application.WorksheetFunction.VLookup, then the
code just
caused an error and stopped right there.

Rbp9ad wrote:

I got a #VALUE error when I put in the following code. I copied this
straight from the module in which I had it. This is only the second
custom
function I wrote so there is probably something that I missed. Any help
would be rgeatly appreciated.

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.WorksheetFunction.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
MsgBox ("Naming Error--just skip?")
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

"Dave Peterson" wrote in message
...
Just curious. What was wrong with the original suggestion?

Rbp9ad wrote:

I am trying to write a function that loops so that I can use vlookup
in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month
name
followed by 1(i.e. January1). The loop should go to the next range
when
it
does not find the value, but currently it does not. It will only
calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr,
1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3,
False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

--

Dave Peterson

--

Dave Peterson

Glad you got it working.

Rbp9ad wrote:

This worked great. I am still new at this so when I started typing
Application.Vlookup and it did not pop out of the library I just assumed
that I needed to add worksheet function to it. I changed the function to
Application.Vlookup and now both RRLookup and SpecLookup work thanks so much
for your help. I did not know that when a worksheet function returns an
error value that the code will stop.

"Dave Peterson" wrote in message
...
Hmmmmmmm.

You changed the code....

Option Explicit
Function SpecLookup(VRN As Variant) As Variant

Application.Volatile True

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
'MsgBox "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

#1. You dropped an Else line right (added back under the msgbox line. In
fact,
I wouldn't use a msgbox except for debugging. You don't want this to show
up
each time the workbook recalculates.

#2. You dropped the "application.volatile true" line. Since you don't
pass all
the ranges that the UDF depends on, if you can a cell value in (say) the
December1 range, your function won't recalculate.

In fact, I'd recalculate before trusting any of this output--this UDF
could be
one calculatation behind.

#3. You added .worksheetfunction to this:

res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)

There is a significant difference on how excel handles error when you use:
Application.WorksheetFunction.VLookup
compared to
application.vlookup

One causes a trappable error that the code has to catch. The other
(easier to
use in my opinion) returns an error you can check.

I used application.vlookup and then checked for that error.

When you changed it to Application.WorksheetFunction.VLookup, then the
code just
caused an error and stopped right there.

Rbp9ad wrote:

I got a #VALUE error when I put in the following code. I copied this
straight from the module in which I had it. This is only the second
custom
function I wrote so there is probably something that I missed. Any help
would be rgeatly appreciated.

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.WorksheetFunction.VLookup(VRN, testRng, 3, False)
If testRng Is Nothing Then
MsgBox ("Naming Error--just skip?")
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res
End Function

"Dave Peterson" wrote in message
...
Just curious. What was wrong with the original suggestion?

Rbp9ad wrote:

I am trying to write a function that loops so that I can use vlookup
in
multiple ranges. The table arrays are on different sheets of the same
workbook. There are twelve of them and I have named them the month
name
followed by 1(i.e. January1). The loop should go to the next range
when
it
does not find the value, but currently it does not. It will only
calculate
for January. If someone could help that would be great.

Option Explicit
Function RRLookup(VRN As Variant) As Variant
Dim testRng As Range
Dim iCtr As Integer
Dim res As Variant

iCtr = 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr, 1),
"mmmm")
& "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3, False)
Set testRng = Nothing
Do Until IsError(res) = False
iCtr = iCtr + 1
Set testRng = ThisWorkbook.Names(Format(DateSerial(2005, iCtr,
1),
"mmmm") & "1").RefersToRange
res = Application.WorksheetFunction.VLookup(VRN, testRng, 3,
False)
Set testRng = Nothing
If iCtr = 13 Then
res = "Not Valid"
End If
Loop
RRLookup = res

End Function

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson