dj44
09-01-2020, 09:02 AM
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
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