Arran,

Did you try my code? I tested it, and it worked. Tom's code did not work
for me: here is a working version:

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


"Arran" wrote in message
...
Hi Tom
Thank you for the code. When I ran it I got a Compile error message 'Loop
without Do'. Could you tell me where I have gone wrong.

Many thanks
Arran


"Tom Ogilvy" wrote:

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
set cell = cell.offset(-1,0)
Loop
msgbox "Ref Number not found"
End sub

--
regards,
Tom Ogilvy



"Arran" wrote:

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