|
Trouble with looping
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 |
Trouble with looping
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 |
Trouble with looping
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 |
Trouble with looping
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 |
Trouble with looping
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 |
Trouble with looping
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 |
Trouble with looping
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" wrote in message ... 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 |
Trouble with looping
If it works for a name of January1, then I don't think it's the original code.
Did you make other changes? Rbp9ad wrote: 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" wrote in message ... 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 -- Dave Peterson |
Trouble with looping
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" wrote in message ... If it works for a name of January1, then I don't think it's the original code. Did you make other changes? Rbp9ad wrote: 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" wrote in message ... 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 -- Dave Peterson |
Trouble with looping
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.) Rbp9ad wrote: 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" wrote in message ... If it works for a name of January1, then I don't think it's the original code. Did you make other changes? Rbp9ad wrote: 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" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com