How do I add Sequential numberring after text

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

This is what I want to do, I have a series of text
scattered throughout my worksheet. I can get the text
sorted by using the filter, what I want to do now is
place numbers after the text in sequential order. For
example it list:

Homework
Homework
Homework

I want it to add number after to look like:

Homework 1
Homework 2
Homework 3

I have a LOT of items in these filters, so it's not
practical for me to manually put in each of the numbers,
there must be a way. Any help would be appreciated.
Thanks.
 
Hi Jerry
try the following macro
Sub foo()
Dim cell As Range
Dim counter As Long
counter = 1
application.screenupdating = false
For Each cell In ActiveSheet.UsedRange
If cell.Value = "Homework" Then
cell.Value = cell.Value & " " & counter
counter = counter + 1
End If
Next
application.screenupdating = True
End Sub
 
Frank,
That worked GREAT, on last question if I can, is there a
way to change the font for the numbers only by adding
that into the macro? If you know of I way, I'd like to
know, I have the text in Ariel, and would like to have
the numbers set up in a different text. I'll keep trying
to figure it out, but will be watching for reply. Thank
you again.
 
Hi Jerry
try something like the follwowing (different font, red color and bold):
Sub foo()
Dim cell As Range
Dim counter As Long
Application.ScreenUpdating = False
counter = 1
For Each cell In ActiveSheet.UsedRange
If cell.Value = "Homework" Then
cell.Value = cell.Value & " " & counter
With cell.Characters(Len(cell.Value) - Len(CStr(counter)) + 1,
_
Len(CStr(counter)))
.Font.Name = "Times New Roman"
.Font.Bold = True
.Font.ColorIndex = 3
End With
counter = counter + 1
End If
Next
Application.ScreenUpdating = True
End Sub
 
Jerry,

I have also boldened and made it Blue just to show what can be done

Sub foo()
Dim cell As Range
Dim counter As Long
counter = 1
Application.ScreenUpdating = False
For Each cell In ActiveSheet.UsedRange
If cell.Value = "Homework" Then
cell.Value = cell.Value & " " & counter
With cell.Characters(10, 3)
.Font.Name = "Comic sans MS"
.Font.Bold = True
.Font.ColorIndex = 5
End With
counter = counter + 1
End If
Next
Application.ScreenUpdating = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Assuming your using Auto-Filter:
where Column C is your "Homework" text
Col B is someother, say numeric non-empty-cell
in a new column enter: and copy down
=C3&SUBTOTAL(3,$B$3:$B3)
HTH
 
Back
Top