• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search Subroutine Needed

frisbeenut

frisbeenut

Member
I am hopeful that someone here can assist me 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, starting 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 digit number beginning with 'A' and search the column for a match. If a match is not found, then return this 6 digit string and exit the function. If it a match is found, then iterate the last character upward through the alphabet 'B', then 'C', ... 'Z' until a match is not found and return the 6 digit string and exit the function. If by chance a match is found with the letter 'Z', then exit the function with an alert box. Note: the column of numbers may contain blank cells.

I am undecided about the length of the column of strings and I might have a column of 5 character strings instead and pass a 4 digit number string instead. Perhaps this function can have a third parameter that specifies the length, or make it so the function does not care about the length as long as the column of strings is one character longer than the number string being given to the function.

Thanks so much for everyone's help.
 
Last edited:
« recursive » ?! Why ? Do you know what that means ?​
​
As I see nothing in your explanation needing a « recursive » function …​
​
 
Hui said:
Please post a sample file with an example of before and after
Click to expand...
The 5 character numeric string will be provided from another subroutine. i.e. 05922 or 15863 or ...

Thanks again
 

Attachments

  • Sample1.xlsm
    10.3 KB · Views: 2
Last edited:
Give some examples of what strings you pass to the function and what are the expected results according to your attachment …​
​
 
The 5 character numeric string will be provided from another subroutine. i.e. 05922 or 15863 or 05922 or ... and the expected results will be A05922, A15863, B05922, ... respectively.
 
Last edited by a moderator:
Sorry, for some reason I didn't see your post #7 (refresh issue ?) so according to it and your attachment,​
when the functions returns an empty string you need to show an « alert box » in your main procedure :​
Code: 
Function RFind$(S$, Rg As Range)
     Dim Rf As Range, C%
     Set Rf = Rg.Find("?" & S, , xlValues, xlWhole, xlByRows, xlPrevious)
      If Rf Is Nothing Then
         RFind = "A" & S
      Else
         C = Asc(Rf.Text) + 1:  If C > 64 And C < 91 Then RFind = Chr(C) & S
         Set Rf = Nothing
      End If
End Function
Do you like it ? So thanks to click on bottom right Like !​
​
 
Marc L said:
Sorry, for some reason I didn't see your post #7 (refresh issue ?) so according to it and your attachment,​
when the functions returns nothing you need to show an « alert box » in your main procedure :​
Code: 
Function RFind$(S$, Rg As Range)
     Dim Rf As Range, C%
     Set Rf = Rg.Find("?" & S, , xlValues, xlWhole, xlByRows, xlPrevious)
      If Rf Is Nothing Then
         RFind = "A" & S
      Else
         C = Asc(Rf.Text) + 1:  If C > 64 And C < 91 Then RFind = Chr(C) & S
         Set Rf = Nothing
      End If
End Function
Do you like it ? So thanks to click on bottom right Like !​
​
Click to expand...
If my explanation was not clear, I apologize, my intention is to skip empty cells and proceed to the next cell in the column. The alert box will happen only after a match is found 26 times, i.e. 05922A, 05922B, ... 05922Z and then exit. I doubt I will ever need to deal with this scenario but since others might be able to use this code for other purposes I thought it would be nice to have an out. Note: letter is at the end of the 5 digit number string.
 
Last edited:
I think a message was somehow deleted. I had posted an updated spreadsheet which seems to have vanished.
 

Attachments

  • Sample2.xlsm
    10.3 KB · Views: 2
I edited your code a little to use the end instead of the beginning and I gave your macro a try on the Sample2.xlsm spreadsheet attached earlier. It does not look like it iterates through the matches 05924A, 05924B, and 05924C and return the 05924D which is the next letter after C. Perhaps I am missing or not understanding something quite right.


Code: 
Function RFind$(S$, Rg As Range)
     Dim Rf As Range, C%
     Set Rf = Rg.Find(S & "?", , xlValues, xlWhole, xlByRows, xlPrevious)
      If Rf Is Nothing Then
         RFind = S & "A"
      Else
         C = Asc(Rf.Text) + 1:  If C > 64 And C < 91 Then RFind = S & Chr(C)
         Set Rf = Nothing
      End If
End Function

Sub test()
Dim str As String
Dim item As String
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("C:C")
str = "'05924"
item = RFind$(str, rng)
MsgBox item
End Sub
 
Last edited:
As the code was made according to post #5 attachment - try with it to see how my function well works -​
you must read the ascii code of the last character rather than the first like for your post #5 attachment​
and the string must be correct so without a quote like str = "05924" …​
​
 
I just edited my previous post - read it again - so my code is yet fully functional according to your posts #5 & 7 …​
​
 
no, my original intention that was reasonably stated said it needs to find the last letter suffix and then add the next letter after. It does not work when there are 05924A, 05924B, and 05924C to produce the result 05924D
 
are you willing to fix it or not? my original post said "If it a match is found, then iterate the last character upward through the alphabet 'B', then 'C', ... 'Z'", I am unable to get your subroutine to do that
 
As according to your post #5 attachment & post #7 explanation, both for the letter in first position,​
the variable C reads the ascii code of the first character.​
So, as you changed again the rule after these posts, you need to read the last character​
via the VBA text function Mid or Right, at beginner level as you can see in VBA help …​
​
 
You must log in or register to reply here.
Back
Top