Search function wanted

[frisbee]

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

 

[offthelip]

Note to get it to work I had to remove the single quote you have put in FINDITEM("'
try this:

Sub test()
txt = FindItem("05924", Worksheets("Sheet1").Range("O:O"))
MsgBox txt

End Sub

Function FindItem(str As String, rng As Range)
Dim numb As String
Dim letr As String

inarr = rng
lastlet = 0
For i = 1 To UBound(inarr, 1)
' split the number for the letter
 numb = Left(inarr(i, 1), 5)
 letr = Right(inarr(i, 1), 1)
 tt = Len(str)
 tt1 = Len(numb)
  If str = numb Then
   ' check for last letter
    If Asc(letr) > Asc(lastlet) Then
     lastlet = letr
    End If
  End If
Next i

 FindItem = Chr(Asc(lastlet) + 1)
 
End Function

 

[frisbee]

If I use Option Explicit then I get a compile error. Is this something that you can easily fix?

Running it without Option Explicit, after I modified I get the result I want by changing the test function to return (itm & txt)

Sub test()
Dim txt As String
Dim itm As String
item = "05924"
txt = FindItem(itm, Worksheets("Sheet1").Range("O:O"))
MsgBox (itm & txt)
End Sub

 

[frisbee]

I wish to thank you very much for your wonderful function, it works great just as I envisioned.

I have been playing around with your function for awhile, how difficult would it be to modify your search function to allow the FindItem function to accept a string that contains both numbers and letters? i.e. 2F204 or F2204 or 2204F or maybe even something weird like ASDFG or AsDfG

 

[frisbee]

Ignore my previous post, the function not working absolutely correct is only happening when the function does not find a match. When there is no match, it is not returning the letter A as expected, it seems to be returning the number 1. Maybe my solution is not very elegant, but if I add the following line just before the End Function it works fine.

FindItem = Chr(Asc(lastlet) + 1)
If FindItem = 1 Then FindItem = "A"
End Function

Thanks a million

 

[frisbee]

Can someone make a minor revision to this subroutine to compensate for when the existing value in Worksheets("Sheet1").Range("O:O") from above ends with a number instead of a letter? What is happening is when the existing value ends with a number, then it does not add the letter "A" at the end, it adds then next higher letter "02204" becomes "022045", and "02209" becomes "02209:" (Note: most values are five numbers with a letter at the end, but there are some that are simply five numbers without a letter.

The way I think it should work when this scenario exists would be to add a letter to the end of it. "02204" becomes "02204A" and "02209" becomes "02209A"

I hope my explanation makes sense.

Thanks in advance.

 

[Rick Rothstein]

Give this function a try...

Function FindItem(S As String, Rng As Range) As String
  If Rng.Find(S & "?", , xlValues, xlWhole, , , , , False) Is Nothing Then
    FindItem = S & "A"
  Else
    FindItem = S & Chr(Evaluate(Replace("MAX(IF(ISNUMBER(SEARCH(""" & S & "?"",@)),CODE(RIGHT(@))))", "@", Intersect(Sheets(Rng.Parent.Name).UsedRange, Rng).Address)) + 1)
  End If
End Function

 

[frisbee]

Wow, it is always wonderful to see a macro that is short and sweet, and efficient. Thanks so much for this greatly improved version.