Increment number after text using VBA

jl2509

Board Regular
Joined
Oct 30, 2015
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have an order form setup on a worksheet that is copied using VBA
At the top of the form in cell "B1", there is Text set as "Order #1 " this number representing the number of the order form.

When I generate a new form using the code already in place, how can I get the text in cell "B1" to increment by 1 from the previous form number. The previous number is always in cell "M1"


Appreciate the help
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try:
Code:
Cells(1,2).Value = "Order #" & cstr(cells(1,13).Value + 1)
 
Upvote 0
Thank you for the response,

Can you provide a little more help on implementing this please within a sub

Code:
Sub Rename()

Cells(1,2).Value = "Order #" & cstr(cells(1,13).Value + 1)

End Sub

Thank you
 
Upvote 0
I do not understand your request, you've indicated it inside a sub called Rename.

If you mean adding to your existing code (without showing it) add that line after the line that copies the sheet.
 
Upvote 0
Apologies, I made a mess there...

Where would I put the line of VBA you proposed within my routine below which was provided by Fluff.

Thank you

Code:
Sub CopyInsertCols()


    Range("C1").Resize(, 8).EntireColumn.Copy
    Range("C1").EntireColumn.Insert
    Range("E4:F4").Select
    Selection.Copy
    Range("A2:B2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Range("A2:B2").Select
      With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("E4:F4").Select
    Application.CutCopyMode = False
    
End Sub
 
Last edited:
Upvote 0
Replace all of that code and try:
Rich (BB code):
Sub CopyInserCols()

    Application.ScreenUpdating = False

    Cells(1, 3).Resize(, 8).EntireColumn.Copy
    Cells(1, 3).EntireColumn.Insert
    Cells(4, 5).Resize(, 2).Copy
    With Cells(2, 1).Resize(, 2)
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        With .Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
    End With
    
    Cells(1, 2).Value = "Order #" & CStr(Cells(1, 13).Value + 1)
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With


End Sub
 
Last edited:
Upvote 0
A function like this might help

Code:
Function NextIndexedString(aString As String, Optional NumericFormat As String = "0") As String
    Dim Prefix As String
    Dim i As Long
    For i = 1 To Len(aString)
        If Not Mid(aString, i, 1) Like "[0-9]" Then
            Prefix = Left(aString, i)
        End If
    Next i
    NextIndexedString = Prefix & Format((Val(Replace(aString, Prefix, vbNullString)) + 1), NumericFormat)
End Function

NextIndexedString("abc3") = "abc4"
NextIndexedString("abc 3") = "abc 4"
NextIndexedString("abc 03") = "abc 4"
NextIndexedString("abc 03", "00") = "abc 04"
 
Upvote 0
Hi

I tried this and I get a runtime error 13 type mismatch

the line which you proposed above (in blue) is highlighted yellow when I debug?

Any thoughts please

Thank you
 
Upvote 0
A function like this might help

Code:
Function NextIndexedString(aString As String, Optional NumericFormat As String = "0") As String
    Dim Prefix As String
    Dim i As Long
    For i = 1 To Len(aString)
        If Not Mid(aString, i, 1) Like "[0-9]" Then
            Prefix = Left(aString, i)
        End If
    Next i
    NextIndexedString = Prefix & Format((Val(Replace(aString, Prefix, vbNullString)) + 1), NumericFormat)
End Function

NextIndexedString("abc3") = "abc4"
NextIndexedString("abc 3") = "abc 4"
NextIndexedString("abc 03") = "abc 4"
NextIndexedString("abc 03", "00") = "abc 04"


Hi,

Thank you for the reply.
I am not sure how to implement this function

Can you elaborate somewhat please

Thank you
 
Upvote 0
Replace all of that code and try:
Rich (BB code):
Sub CopyInserCols()

    Application.ScreenUpdating = False

    Cells(1, 3).Resize(, 8).EntireColumn.Copy
    Cells(1, 3).EntireColumn.Insert
    Cells(4, 5).Resize(, 2).Copy
    With Cells(2, 1).Resize(, 2)
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        With .Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
    End With
    
    Cells(1, 2).Value = "Order #" & CStr(Cells(1, 13).Value + 1)
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With


End Sub

Hi

Running this vba gives me an error, using the same line in my original code the same error?

Any thoughts please.

Thankyou
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top