Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number on command
There was a typo:
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 -- Regards, Tom Ogilvy "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Increment Number | Excel Discussion (Misc queries) | |||
Increment number on command | Excel Programming | |||
Increment number on command | Excel Programming | |||
increment number by code | Excel Programming | |||
Increment number by 1 | Excel Programming |