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

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

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