Looking for a better way - Serial to local machine number
I have a list of machine serial numbers from a national database that I want
to change to a local number in a UDF. Right now I am using a Select Case statement to do that. This works fine for the small set I'm using right now. However, I would like to expand this to a larger set of numbers. What would be the best way to do this? Thanks - Mike Select Case vDecSerial Case 53: vLocal = 1 Case 72: vLocal = 2 Case 95: vLocal = 3 Case 114: vLocal = 4 Case 162: vLocal = 5 Case 2044: vLocal = 6 Case 2068: vLocal = 7 Case 2346: vLocal = 8 End Select |
Looking for a better way - Serial to local machine number
Maybe you could put the data in a hidden worksheet. And use =vlookup() to
return the value you need. dim TableLookup as Range dim vLocal as variant 'could return an error set tableLookup = worksheets("sheet9999").range("a:b") res = application.vlookup(vDecSerial, tablelookup,2,false) if iserror(res) then 'not found vLocal = 9999999 'what should happen? end if mikebres wrote: I have a list of machine serial numbers from a national database that I want to change to a local number in a UDF. Right now I am using a Select Case statement to do that. This works fine for the small set I'm using right now. However, I would like to expand this to a larger set of numbers. What would be the best way to do this? Thanks - Mike Select Case vDecSerial Case 53: vLocal = 1 Case 72: vLocal = 2 Case 95: vLocal = 3 Case 114: vLocal = 4 Case 162: vLocal = 5 Case 2044: vLocal = 6 Case 2068: vLocal = 7 Case 2346: vLocal = 8 End Select -- Dave Peterson |
Looking for a better way - Serial to local machine number
Thank you Dave,
That should work. "Dave Peterson" wrote: Maybe you could put the data in a hidden worksheet. And use =vlookup() to return the value you need. dim TableLookup as Range dim vLocal as variant 'could return an error set tableLookup = worksheets("sheet9999").range("a:b") res = application.vlookup(vDecSerial, tablelookup,2,false) if iserror(res) then 'not found vLocal = 9999999 'what should happen? end if mikebres wrote: I have a list of machine serial numbers from a national database that I want to change to a local number in a UDF. Right now I am using a Select Case statement to do that. This works fine for the small set I'm using right now. However, I would like to expand this to a larger set of numbers. What would be the best way to do this? Thanks - Mike Select Case vDecSerial Case 53: vLocal = 1 Case 72: vLocal = 2 Case 95: vLocal = 3 Case 114: vLocal = 4 Case 162: vLocal = 5 Case 2044: vLocal = 6 Case 2068: vLocal = 7 Case 2346: vLocal = 8 End Select -- Dave Peterson |
All times are GMT +1. The time now is 04:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com