Trouble with looping

[Rbp9ad]

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]

Just curious. What was wrong with the original suggestion?

 

[Rbp9ad]

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]

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]

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]

Glad you got it working.

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.

 

[Rbp9ad]

I changed the following line of code to change the function from the test
version to the one that I actually want.
Set testRng = Workbooks("Receiving Report Log
2005.xls").Names(Format(DateSerial(2005, iCtr, 1), "mmmm") &
"1").RefersToRange
It gives me a #VALUE response on anything that is not in January.

 

[Dave Peterson]

If it works for a name of January1, then I don't think it's the original code.

Did you make other changes?

I changed the following line of code to change the function from the test
version to the one that I actually want.
Set testRng = Workbooks("Receiving Report Log
2005.xls").Names(Format(DateSerial(2005, iCtr, 1), "mmmm") &
"1").RefersToRange
It gives me a #VALUE response on anything that is not in January.

 

[Rbp9ad]

I copied and pasted this code from my module. I opened RR log file before I
tested the function.

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 = 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]

Hmmmmmmmm.

I screwed up when I pasted the corrected version in. (But the original code was
fine!)

Back to the original suggestion (with your workbook name change and function
name change):

Function SpecLookup(idNumber As Variant) As Variant

Application.Volatile True

Dim myLookupRng As Range
Dim myMonthCol As Range
Dim iCtr As Long
Dim testRng As Range
Dim res As Variant

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

If testRng Is Nothing Then
'naming error --just skip???
Else
res = Application.VLookup(idNumber, testRng, 3, False)
If IsError(res) Then
'keep looking
Else
Exit For 'found it
End If
End If
Next iCtr

SpecLookup = res

End Function

(Notice that the vlookup() was moved after the test to see if that range name
existed.)

I copied and pasted this code from my module. I opened RR log file before I
tested the function.

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 = 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