VBA: find duplicates and change

[Zizzo]

I am a beginner at Excel VBA. I just wanna know what code I am going to use for changing the value of the selected column if found duplicate. For example, I want to create a code that changes the duplicates found from their original name to their original name + "_01" (or something similar). It is important that the code changes duplicates in the original row - not copying the row and pasting the result into some other column. I have tried different IF and countIF functions but have yet to succeed in VBA.

My range will be (A1:A100)


Example:

Row A	Row A
aa	aa
cc	cc
dd	dd
xx	xx
ss	ss
ee	ee
ff	ff
rr	rr
qq	qq
aa	aa_01
cc	cc_01
zz	zz
ss	ss_01
zz	zz_01

 

[Flaiban]

Try this code...

Sub FindandReplace()
    Dim cell As Range
    With Worksheets("Sheet1")
        With .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
            For Each cell In .Cells
                    cell.Value = cell.Value & "_" & WorksheetFunction.CountIf(.Parent.Range(.Cells(1), cell), cell.Value & "_*")
            Next
            .Replace what:="_0", replacement:="", lookat:=xlPart
        End With
    End With
End Sub