Hi, Y'all,
New to the forum and kind of a novice. I have about 70-some-odd workbooks with a column that has a RouteID like so:
BLU1
BLU2
...
BLU39
GRE1
GRE2
...
GRE40
ORA1
...etc
Need to renumber to BLU01, BLU02, GRE01, GRE02, etc, cuz as y'all know, when you sort with numbers like I have, it comes out BLU1, BLU11, BLU12, etc.
I've tried the following, to no avail, as you can see I've tried nested and un-, and pound signs and stars as the wildcards. It works to convert the first series (BLU), but won't cycle past that to select the next route in the series. Some of these sheets have up to 12 Route codes with as many as 38-43 ID's per Route, and they vary, hence the need for Wilds. Any ideas? I am also posting a sample workbook with the code attached if ya wanna play around with it. Thanks in advnce for any help!
Sub RenumberRouteID()
' Sub RenumberRouteID
' Renumbers to "01" etc. for sorting
'Declare Variables
Dim Route
Dim RouteID As String
Dim ColorCount
Dim RowCount
ColorCount = 0
RowCount = 0
'Name Column A Range
Sheets("CONSOL").Select
Range("A4:A2000").Select
ActiveWorkbook.Names.Add Name:="CONSRouteID", RefersToR1C1:="=CONSOL!R4C1:R2000C1"
'Activate first Cell in Range
Range("A4").Select
For Each Route In Range("CONSRouteID").Cells
'If Route.FormulaR1C1 = "RouteID" Like "BLU##" Then
If Route.FormulaR1C1 = "BLU1" Then
ActiveCell.FormulaR1C1 = "BLU01"
Selection.AutoFill Destination:=Range(Cells(4, 1), Cells(4 + 8, 1)), Type:=xlFillDefault
End If
'RowCount = RowCount + 1
'Range(Cells(4 + RowCount, 1)).Select
'End If
Next
For Each Route In Range("CONSRouteID").Cells
If Route.Value = "BLU*" Then
RowCount = RowCount + 1
Range(Cells(4 + RowCount, 1)).Select
End If
Next
End Sub
200809_MONTHLY_HELP.xlsm
Bookmarks