String Manipulation with VBA

[mlstewart]

I have a form called “RenewalForm” with a text box called “Agreement” that contains a string. The string represents an agreement/contract which is given a number when it is created (ex: 01, 02, 03, etc). These agreements are renewed from time to time and when one gets renewed, the letter "R" needs to be placed behind the agreement number (ex: 01R, 02R, 03R, etc). The next time the agreement gets renewed, the number "2" needs to be placed behind the "R" (ex: 01R2, 02R2, 03R2, etc). Now, each time the agreement is renewed, the number after the "R" needs to be incremented by one (ex: 01R3, 01R4, 01R5, etc). Starting from the beginning the agreements go something like this:

01
01R
01R2
01R3
01R4
Etc.

Sometimes these agreements are combined and they are separated by a dash. Some examples are:

01-02R
03R-04R-05R
01R-02R2
01R-02R3-03

It could be any combination.

The original agreement number that is before the “R” will never change (01, 02, 03, etc). The only changes to the string will be either adding an “R” for the first renewal and after that, only the number after the R will change.


When it comes time for the agreement(s) to be renewed, these are the changes that need to be made to the string:

1)If the string is just a number like “01” then I want to place an “R” behind the one to show that it has been renewed for the first time like this: 01R

2)If the string is “01R” then I want it to place a “2” behind the “R” to show that it has been renewed for the second time like this: 01R2 (Each time it is renewed after the second time, the number after the R will need to be incremented by one like this: 01R3, 01R4, 01R5, etc)

3)If several agreements have been combined it will be a string with the dashes like "01-02R3-03R" where each section separated by the "-" needs to be renewed like this: "01R-02R4-03R2"

Here is what changed:
“01” to “01R”
“02R3” to “02R4”
“03R” to “03R2”

The new string will be "01R-02R4-03R2"


I have a button on the “RenewalForm” for the user to click when the agreement needs to be renewed. This button opens another form called "RenewalFormSte p2RENEW". In the On Click event for the button, I want it to get the string in the text box “Agreement” on the form “RenewalForm” and renew it like I explained above and then place the renewed agreement number in a text box called “NewAgreement” on the form "RenewalFormSte p2RENEW"

Thank you very much in advance for you help!
mlstewart

[ADezii]

I threw together a Function, along with some examples, that should provide tha basic functionality that youo requested. Simply pass to the Function the Agreement Number to be modified:

Public Function fCalcNewAgreement(strString As String) As String
Dim intPosOfR As Integer

If Len(strString) = 0 Then Exit Function

intPosOfR = InStr(strString, "R")       'Position of 'R' in String

If intPosOfR > 0 Then      'a 'R' is in the String
  If Right$(strString, 1) = "R" Then    'trailing 'R'
    fCalcNewAgreement = strString & "2"
   Else     'R' in String but not Trailing
    'Extract the characters up to and including the 'R', then increment
    'the numeric Value after the 'R' by +1, then Append it to the Base
    fCalcNewAgreement = Left$(strString, intPosOfR) & _
                        CStr(Val(Mid$(strString, intPosOfR + 1)) + 1)
 End If
Else    'a 'R' is NOT in the String, so Append one
  fCalcNewAgreement = strString & "R"
End If
End Function

Debug.Print fCalcNewAgreement("01")
01R

Debug.Print fCalcNewAgreement("01R")
01R2

Debug.Print fCalcNewAgreement("01R5")
01R6

Debug.Print fCalcNewAgreement("0972")
0972R

Deb

ug.Print fCalcNewAgreement("502R999")
502R1000

Debug.Print fCalcNewAgreement("046R")
046R2

[NeoPa]

A fun question :)

Public Function Renew(strID As String) As String
    Dim strItem As String
    Dim intX As Integer
    Dim varAry As Variant

    varAry = Split(strID, "-")
    For intX = LBound(varAry) To UBound(varAry)
        strItem = varAry(intX)
        Select Case Len(strItem)
        Case Is > 3
            varAry(intX) = Left(strItem, 3) & Val(Mid(strItem, 4)) + 1
        Case 3
            varAry(intX) = strItem & "2"
        Case Else
            varAry(intX) = strItem & "R"
        End Select
    Next intX
    Renew = Join(varAry, "-")
End Function

[mlstewart]

Thanks so much for the help. I am working on it now and will let you know how it turns out. THANKS!!!

[mlstewart]

Ok, I have it working. Thank you both so much. I ended up using the code from NeoPa. It did exactly what I asked for in my original post. However, I ended up having to tweek it a little because I needed it to do a little something different than my original post asked for. Actually, ADezii's code was very close to what I ended up needing but there was one problem that I couldn't figure out how to fix. ADezii... your code worked great on everything until I had the combination "01-02R-03" and your code gave me "01-02R-2" and I needed it to give me "01-02R2-03R"

To explain:

01-02R got combined with 03 to make 01-02R-03 and that whole thing needed to be renewed to 01-02R2-03R

It doesn't really matter now because I played around with NeoPa's code and it is now doing what I need.

After you two posted your code, I really tried to break the code down to fully understand what you were doing. The comments in NeoPa's code really helped a lot. I want to thank both of you for your help. I really learned a lot by examining and really trying to understand and learn from you two.

THANKS!!!

[NeoPa]

mlstewart:
After you two posted your code, I really tried to break the code down to fully understand what you were doing. The comments in NeoPa's code really helped a lot. I want to thank both of you for your help. I really learned a lot by examining and really trying to understand and learn from you two.

Excellent attitude. You deserve to pick things up if you go in like that :)

mlstewart:
01-02R got combined with 03 to make 01-02R-03 and that whole thing needed to be renewed to 01-02R2-03R

Surely, from your explanation earlier, that should result in "01R-02R2-03R"?