Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |