Find/replace help
 

Hi all,

Newbie here, I've just started writing macros for Excel and I'm having
problems with one which I hope you can help me with.

I've got a list of numbered cases in my sheet like this:

DPB_001
DPB_002
DPB_003
......
......
DPB_100

I'm trying to write a macro which can renumber them, starting at any
point I choose and then basically incrementing each number by 1. So
say I started at DPB_002, I'd want that to become DPB_003 and every
other one until the end of the sheet to become 1 larger (in this
example ending in DPB_101).

My problem is that the sheet has grouping in it, so my idea is to
start at the active cell and then search for the next occurance of
DPB_* then add 1 to it then process the next one until the end of the
sheet.

My VB isn't good yet so if someone could give me a few tips on how to
write a macro like this then I'd be grateful.

Thanks

Steve

Find/replace help
 

That works perfectly :-) Thanks very much.

Steve

"Tom Ogilvy" wrote in message ...
This assumes no spaces in your list.

Option Explicit
Sub SetNumbers()
Dim iNum As Long, cnt As Long
Dim lNum As Long, Preface As String
Dim sStr As String, rng As Range
Dim iloc As Long
sStr = ActiveCell.Value
iNum = GetNumber(sStr, cnt, iloc, Preface)

lNum = Application.InputBox("Enter Start Number", _
"Start Number", iNum + 1, , , , , 1)

Set rng = ActiveCell

Do While Not IsEmpty(rng)
If UCase(Left(rng, iloc - 1)) = UCase(Preface) Then
rng.Value = Preface & _
Format(lNum, String(cnt, "0"))
lNum = lNum + 1
End If
Set rng = rng.Offset(1, 0)
Loop
End Sub

Public Function GetNumber(sStr As String, cnt As Long, _
iloc As Long, sStr2 As String)
Dim sChr As String
Dim sStr1 As String
Dim i As Long
For i = 1 To Len(sStr)
sChr = Mid(sStr, i, 1)
If IsNumeric(sChr) Then
If iloc = 0 Then iloc = i
sStr1 = sStr1 & sChr
ElseIf iloc = 0 Then
sStr2 = sStr2 & sChr
End If
Next
cnt = Len(sStr1)
GetNumber = CLng(sStr1)
End Function

--
Regards,
Tom Ogilvy