Next number with text and leading 0's VBA

[rooster05]

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

 

[Norie]

Try this.

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

 

[rooster05]

Works a treat, thanks for the speedy reply