Numbering repeated values in column in Excel using VBA

Question

I have a column with varying values and some of these values can sometimes be repeated, so if there are two of the same value I need to have the first value followed by 1 and the second followed by 2.

For Example:

Apple1
Apple2
Lemon1
Apple3
Pear1
Lemon2
Apple4
Orange1
Pear2

I've tried using nested if loops but I can't seem to find an efficient way to do this.

Answer 1

You can use 2 loops to go through all the elements. Btw, you can add 1 more step to check numeric the last character and skip for faster process