Sequentially Number Placeholder in Cells

[dj44]

folks,

good day


any one know how i can sequentially number this placeholder the %%

the cell has


%% Apple
%% Banana
%% Fruit

After Numbering

1 Apple
2 Banana
3 Fruit
4
etc

I have researched and cant find this simple task,

I am trying to think of a vba solution, becuase i had 1000 rows and i put formulas in it and i couldnt use my workbook the memory increased to 12MB i had to destroy my good work later

How can i just number these placeholders in the colum

thank you very much for any advice

[dj44]

I managed to work out

Sub SequentialCellNumber()

Dim c As Range
Dim cph As String
Dim lngIndex As Long: lngIndex = 1

cph = "%%"

For Each c In Selection

If c.Value <> "" Then c.Value = c.Value & cph & lngIndex

lngIndex = lngIndex + 1

Next
End Sub

So i need to do a search for the %% and then number them

[dj44]

Happy to report i managed to work it out finally


if any one needs to number a bunch of %% in theor cells i say this may suffice

Sub Sequentially_Number_Cells()

Dim lngIndex As Long: lngIndex = 1

Dim oLastRow As Long, i As Long

oLastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To oLastRow

With Range("A" & i)
.Value = Replace(.Value, "%%", lngIndex)     ' << Search and Replace here

lngIndex = lngIndex + 1
End With
Next i

End Sub

so long as it does the job and i dont have to use that dreaded formula

happy weekend all

[mdmackillop]

No looping

Sub test()
Dim Rng As Range
Set Rng = Cells(1, 1).CurrentRegion
Rng.Offset(, 1).FormulaR1C1 = "=SUBSTITUTE(RC[-1],""%%"",ROW())"
Rng.Value = Rng.Offset(, 1).Value
Rng.Offset(, 1).ClearContents
End Sub

[dj44]

Another way to do it thank you M,

well i was very upset that Excel gave up on me.

Never in my days had i known 1000 rows would cause such mischief.

but then I was being very ambitious concatnating strings with all sorts.

excel has millions of rows and it gave up on my 1000 rows

oh well im glad i only did 2 columns of 1000 rows when it self combusted on me