Bernie and Tom

Thank you both for your help. Bernie apologies for appearing rude by not
responding. Had one of those days, constant interruptions & nothiing got
finished.
In the end guys I have 2 working versions for the price of 1, both worked
under all the test coditions I applied. Both listed below for ease of
reference.

Many thanks

Arran

Private Sub Commandbutton1_click()
Dim cell As Range
Dim s As String, s1 As String
Dim snm As String
Set cell = ActiveCell.Offset(-1, 0)
Do While cell.Row 1
If Len(cell) 4 And Len(cell) < 8 Then
s = Right(cell, 4)
s1 = cell.Value
If IsNumeric(s) Then
snm = Format(CLng(s) + 5, "0000")
s1 = Replace(s1, s, snm)
ActiveCell.Value = s1
ActiveCell.Offset(0, 1).Select
Exit Sub
End If
End If
Set cell = cell.Offset(-1, 0)
Loop
MsgBox "Ref Number not found"
End Sub

and this one

Sub Working()
Dim cell As Range
Dim s As String, s1 As String
Dim snm As String
Set cell = ActiveCell.Offset(-1, 0)
While cell.Row 1
If Len(cell) 4 And Len(cell) < 8 Then
s = Right(cell, 4)
s1 = cell.Value
If IsNumeric(s) Then
snm = Format(CLng(s) + 5, "0000")
s1 = Replace(s1, s, snm)
ActiveCell.Value = s1
ActiveCell.Offset(0, 1).Select
Exit Sub
End If
End If
Set cell = cell.Offset(-1, 0)
Wend
MsgBox "Ref Number not found"

End Sub

"Bernie Deitrick" wrote:

Tom,

Yikes! My apologies, Tom. I didn't say I was a _good_ tester - it works
90% of the time (for large populations of reference numbers) and 100% of the
time on the value that I used to test it... ;-)

Bernie's will work with a straight 4 digit number

Which shouldn't be there per the original post.

And where's that darned "retract post" command when you need it....

Bernie