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

Thanks for the alert Bernie. Guess I left out a line.

Anyway, to be just as helpful:

I tested it, and it worked.

What did you get if the reference value was rr0022

Yours returned rr27 - for me. My guess would be that wouldn't be
correct, so

Bernie's code did not work
for me: here is a working version: <g

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) _
& Format(myVal + 5, "0000")
ActiveCell.Offset(0, 1).Select
Exit Sub
notValue:
Resume TryNext:
TryNext:
Next myRow

End Sub

Lightly tested by me. Bernie's will work with a straight 4 digit number
while mine will look only for prefixed 4 digit numbers, so his may be more
desirable if that is a requirement.

--
Regards,
Tom Ogilvy

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

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

If you find it let me know<g

What we need is a "are you sure you have really thought this out?" when we hit
Send.


Gord

On Mon, 8 Jan 2007 21:55:24 -0500, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

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