Next number with text and leading 0's VBA

rooster05

New Member
Joined
Mar 4, 2017
Messages
34
Hi all,
i have the following code which puts a sequential number in column P, which is fine.

My question is how do i prefix this with the letters VOMH and then have leading 0's (zeros) in the column, ie VOHM0001, VOMH0002 and so on.

Any help appreciated


Private Sub CommandButton2_Click()
If Application.WorksheetFunction.CountA("P:P") = 0 Then
[P1].Select
Else
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks)(1, 16).Select
If Err <> 0 Then
On Error GoTo 0
[P150].End(xlUp)(1, 16).Select
End If
On Error GoTo 0
End If
With Range("P" & Rows.Count).End(xlUp).Offset(1)
.Value = .Offset(-1).Value + 1
End With
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,539
Office Version
365
Platform
Windows
Try this.
Code:
Private Sub CommandButton2_Click()
    If Application.WorksheetFunction.CountA("P:P") = 0 Then
        [P1].Select
    Else
        On Error Resume Next
        Columns(1).SpecialCells(xlCellTypeBlanks)(1, 16).Select
        If Err <> 0 Then
            On Error GoTo 0
            [P150].End(xlUp)(1, 16).Select
        End If
        On Error GoTo 0
    End If

    With Range("P" & Rows.Count).End(xlUp).Offset(1)
        .Value = Format(Val(Mid(.Offset(-1).Value, 5)) + 1, """VOHM""0000")
    End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,364
Messages
5,444,025
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top