Sequentially Number Search Using an Array

[dj44]

Good day folks, :)

I have a simple problem that has me a bit confused.

I have created an array to sequentially number my array items

It is numbering them wrong.

It continues to number the next array item when it should start from 0 again.

It may be a simple loop mistake, but I couldn’t work it after many atempts.





Sub Sequentially_Number()

Dim oCell As Range
Dim i, j As Long
Dim vSearch As Variant


vSearch = Array("Apple", "Pear")


For j = LBound(vSearch) To UBound(vSearch)

i = 0

For Each oCell In ActiveSheet.Range("A1:A20").Cells

oCell.Replace What:=vSearch(j), Replacement:=vSearch(j) & i
i = i + 1
Next oCell


Next j




End Sub







Current Result
Apple1
Apple2
Apple3
Apple4
Apple5
Pear7
Pear8
Pear9
Pear10
Pear11
Pear12



Results Should be
Apple1
Apple2
Apple3
Apple4
Apple5
Pear1
Pear2
Pear3
Pear4
Pear5



The next item in the array should start numbering from 0 again, but that’s not happening

I would appreciate if some one may be able to look at this misbehaving loop.

Thank you

[mikerickson]

As written, the I variable increases once for every cell, regardless of whether the search term is found or not.

Try

dim oldVal as string


For Each oCell In ActiveSheet.Range("A1:A20").Cells
oldVal = oCell.Value
oCell.Replace What:=vSearch(j), Replacement:=vSearch(j) & i
if oldVal<>oCell.Value Then i = i + 1
Next oCell

[dj44]

Thanks Mike,

for this speedy alternative does the job right.

I just assumed it is looking for the search term and then numbering, and then when the next array term comes it starts from 0 again.

Oh well, alls not what it seems.

The numbering is correct now.

Thanks so much and have a smashing day! :)

[snb]

Avoid worksheet interaction (reading/writing/searching/replacing):


Sub M_snb()
sn = Range("A1:A20")

For j = 1 To UBound(sn)
If InStr("applepear", sn(j, 1)) Then
If sn(j, 1) = "apple" Then y = y + 1
If sn(j, 1) = "pear" Then x = x + 1
sn(j, 1) = sn(j, 1) & IIf(sn(j, 1) = "apple", y, x)
End If
Next

Range("F1:F20") = sn
End Sub

[dj44]

Thank you for this alternative version as well, I will sure test it out :)