Increment number on command
 

Arran,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub ArranSub()
Dim myRow As Long
Dim myValue As String
Dim myVal As Integer

For myRow = ActiveCell.Row To 1 Step -1
On Error GoTo notValue
myValue = Cells(myRow, ActiveCell.Column).Value
myVal = CInt(Right(myValue, 4))
ActiveCell.Value = Left(myValue, Len(myValue) - 4) & myVal + 5
ActiveCell.Offset(0, 1).Select
Exit Sub
notValue:
Resume TryNext:
TryNext:
Next myRow

End Sub


"Arran" wrote in message
...
Can any one help me out with some code that will start by looking back up the
column from the point of the active cell, find the last occurance of a Ref#,
increase it by 5, place the result in the active cell and finish by moving
one cell to the right.
Few points.
1) Ref# prefixed with maybe 1,2 or 3 letters but always followed by a 4
digit number, 0001 - 9999.
2)There will also be blank cells and ones containing just text in the column
3)Will run the code from a Command button on the Sheet

Any help gratefully received
Arran