Add number sequentially to string based on formula

**nickmax1** · 09-07-2012, 06:48 AM

I have the following macro that runs beautifully that replicates a sheet in my workbook and removes rows from the original sheet based on certain criteria.
I wish to amend this code so that the resulting string that remains in cells A17:A190 is effected by the following formula:

If A18 is blank then leave as blank

=IF(MID(A18,8,1)="/" then left(a18,8)+mid(a18,9,3) +1
so if there is P009959/004 in A18 the macro places P009959/005 in its place.

=IF(LEFT(A18,1)="S" then left(a18,10)+mid(a18,10,3)+1
so if there is SP0091377/001A in A18 the macro places SP0091377/002 in its place

otherwise
LEFT(A18,10)

here is my macro so far

Sub SheetMaker()
Dim NewSheet As Worksheet, NewName As String
Dim shName As Variant, v As Variant

shName = Application.InputBox(Prompt:="Enter sheet name:", Type:=2)
If shName = False Then Exit Sub

For Each s In Sheets
    If s.Name = shName Then
        GoTo GoodToGo
        Else
        MsgBox "Sheet Doesnt Exist"
        Exit Sub
        
    End If
Next
Exit Sub
GoodToGo:
ary = Split(shName, " ")
ary(1) = ary(1) + 1
NewName = Join(ary, " ")
Sheets(shName).Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = NewName

For l = 190 To 17 Step -1
    v = Cells(l, "I").Value
    If v = "NTU" Or v = "Declined" Or v = "Non-Renewed" Or v = "Extended" Then
        Cells(l, "I").EntireRow.Clear
    End If
Next
End Sub



copytest.xls

**nickmax1** · 09-07-2012, 05:28 PM

is this possible?

**jindon** · 09-07-2012, 08:12 PM

Is this kind of what you are after?

Sub teset3()
    Dim a, i As Long
    With Range("a18", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        For i = 1 To UBound(a, 1)
            If a(i, 1) <> "" Then
                If a(i, 1) Like "*/*" Then
                    a(i, 1) = Trim$(Split(a(i, 1), "/")(0)) & "/" & _
                    Format$(Val(Split(a(i, 1), "/")(1)) + 1, "000")
                Else
                    a(i, 1) = Left(a(i, 1), 10)
                End If
            End If
        Next
        .Value = a
    End With
End Sub

**nickmax1** · 09-09-2012, 04:11 AM

jindon that works great for the 1st formula ( ie it takes care of the string with a "/" in it) but can the code be amended to deal with excel formula 2 ( dealing with string beginning with S)>>?

**jindon** · 09-09-2012, 04:15 AM

I don't understand...

It should convert as you wanted regardless of "S"...

Can you explain further?

**nickmax1** · 09-09-2012, 05:57 AM

If I read your code right it only deals with string with a / in it. I if you see my formulas above I also want to deal with strings starting with a S.
Try the formula on my Aug 2012 sheet and you can see not all strings are dealt with

**jindon** · 09-09-2012, 06:04 AM

Your formulas doesn't make sense to me.

**nickmax1** · 09-09-2012, 07:01 AM

ok i will try to explain.
Your code works brilliantly for my formula 1, adding a number to the string that has a "/" in there somewhere.

I also want it to add a number to the string when it starts with the letter S (for example A42 has one). For the strings without a / in it or that begin with an S, such as P01132F11A, then this is to be converted to P01132F12A

**jindon** · 09-09-2012, 07:05 AM

OK, can you upload the file with before / after format for possible patterns?

**nickmax1** · 09-09-2012, 07:20 AM

BeforeAfter.xlsx

heres a few examples with what they are before and what i am hoping the macro will change them to.

Also if the cell is blank the macro needs to do nothing.

thanks!

**jindon** · 09-09-2012, 07:33 AM

Originally Posted by nickmax1

I have removed any " - R" entries so that isnt an issue going forward.

Then,

Sub test()
    Dim a, i As Long, temp As String
    With Range("a18", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            For i = 1 To UBound(a, 1)
                .Pattern = "(.*\D)(\d+)(\D+)?$"
                If .test(a(i, 1)) Then
                    temp = .Replace(a(i, 1), "$2")
                    a(i, 1) = .Replace(a(i, 1), "$1" & _
                    Format$(Val(temp) + 1, String(Len(temp), "0")) & "$3")
                End If
            Next
        End With
        .Value = a
    End With
End Sub

**nickmax1** · 09-09-2012, 08:29 AM

works brilliantly!!

i have NO idea how your code does what it does, but it works! Can you run through what each line of code does?

**nickmax1** · 09-09-2012, 08:24 AM

I have removed any " - R" entries so that isnt an issue going forward.

**jindon** · 09-09-2012, 08:39 AM

It adds 1 to the last appeared block of numbers.

"(.*\D)(\d+)(\D+)?$"

\d = Number
\D = Non numeric character

This pattern will match to

Any characters + numbers with or without any character(s) that is/are not number at the end.
And you can refer back to each sub-matches that is inside the set of brackets afterwords.

For more details, google with the key words like "VBA" "Regular Expression" "Meta Character" etc.

**nickmax1** · 09-09-2012, 11:19 AM

thank you so much for your prompt help and explanation!

Add number sequentially to string based on formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Re: Add number sequentially to string based on formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions