I am hopeful that someone here can assist in writing a VBA search function that accepts 2 parameters, a string and a column range. I have a column of string values that are 6 characters long that begin with 5 numbers and ends with a capital letter, beginning with the letter 'A".
I want to pass a 5 digit number string and a column range to this function. The function will add a single capital letter to end of this 5 number string with an 'A' appended to the end of the string. Starting with the letter "A" and search the column for a match.
If a match is not found, then return this newly created 6 digit string (original string with an "A" at the end) and exit the function.
If this 6 character string finds a match in the specified range, then iterate the last character upward changing the letter "A" to the letter "B", and repeat the search. Repeat the search through the alphabet 'B', then 'C', ... 'Z' until a match is not found and return the 6 character string value and exit the function and return the 6 character string value.
If by chance a match is found with the letter 'Z', then exit the function with an alert box.
Note: Column may contain blank cells. Additionally, the cell is formatted as text and their values might have leading zeros.
Function Find_Item_Number (str$, rng as Range)
...
End Function
FindItem ("'05924" , Worksheets("Sheet1").Range("C:C"))
searches the following in Column "C"
94413A
05924A
05922A
05924B
05905A
05924C
05922B
Should return the string 05924D
Thanks in Advance
I want to pass a 5 digit number string and a column range to this function. The function will add a single capital letter to end of this 5 number string with an 'A' appended to the end of the string. Starting with the letter "A" and search the column for a match.
If a match is not found, then return this newly created 6 digit string (original string with an "A" at the end) and exit the function.
If this 6 character string finds a match in the specified range, then iterate the last character upward changing the letter "A" to the letter "B", and repeat the search. Repeat the search through the alphabet 'B', then 'C', ... 'Z' until a match is not found and return the 6 character string value and exit the function and return the 6 character string value.
If by chance a match is found with the letter 'Z', then exit the function with an alert box.
Note: Column may contain blank cells. Additionally, the cell is formatted as text and their values might have leading zeros.
Function Find_Item_Number (str$, rng as Range)
...
End Function
FindItem ("'05924" , Worksheets("Sheet1").Range("C:C"))
searches the following in Column "C"
94413A
05924A
05922A
05924B
05905A
05924C
05922B
Should return the string 05924D
Thanks in Advance