Slicemahn
01-07-2008, 02:14 PM
Hello Everyone!
I am trying to use this UDF in order to create a unique key that will upload to a database table.
I keep getting a Run-time error 5 Invalid call or argument error.
The function will look at a cell1, look at all the characters following the ":" and then by the SELECT CASE statements find the corresponding number that would be the first portion of the string.
Cell1 & Cell2 are then put together (concatenated) in order to yield the unique ID.
Public Function CreatePeopleID(cell1 As Variant, Cell2 As Variant) As Variant
Dim cellholder As Variant
Select Case Mid(cell1, InStr(cell1, ":"), Len(cell1) - InStr(cell1, ":"))
Case Bloor
cellholder = 1
Case "Erin Mills"
cellholder = 2
Case Burnaby
cellholder = 3
Case Calgary
cellholder = 4
Case Kitchener
cellholder = 5
Case Moncton
cellholder = 6
Case Montreal
cellholder = 7
Case Ottawa
cellholder = 8
Case "Richmond Hill"
cellholder = 9
End Select
CreatePeopleID = cellholder & Cell2.Value
End Function
I am trying to use this UDF in order to create a unique key that will upload to a database table.
I keep getting a Run-time error 5 Invalid call or argument error.
The function will look at a cell1, look at all the characters following the ":" and then by the SELECT CASE statements find the corresponding number that would be the first portion of the string.
Cell1 & Cell2 are then put together (concatenated) in order to yield the unique ID.
Public Function CreatePeopleID(cell1 As Variant, Cell2 As Variant) As Variant
Dim cellholder As Variant
Select Case Mid(cell1, InStr(cell1, ":"), Len(cell1) - InStr(cell1, ":"))
Case Bloor
cellholder = 1
Case "Erin Mills"
cellholder = 2
Case Burnaby
cellholder = 3
Case Calgary
cellholder = 4
Case Kitchener
cellholder = 5
Case Moncton
cellholder = 6
Case Montreal
cellholder = 7
Case Ottawa
cellholder = 8
Case "Richmond Hill"
cellholder = 9
End Select
CreatePeopleID = cellholder & Cell2.Value
End Function