Countif Formula using For Each ....Next loop

[vranjit138]

Hi Friends

i have invoice numbers many of them mostly repeating in Column A
in Column how can i show which invoice repeats how many times and
the time it repeats using macro..
Iam pretty comfortable with For Each ... Next loop but iam unable to
use it here. What i trying is get the countif fuction in this loop

Can anyone help me with this loop

Regards
ranjit

 

[ravishankar]

Hi
Paste the following codes in the macro window ( alt F11)

Sub Macro1()
'find unique invoice numbers
  x = Cells(Rows.Count, 1).End(xlUp).Row
  a = "A1:A" & x
    Range(a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1" _
        ), Unique:=True
'count their occurance
        y = Cells(Rows.Count, 2).End(xlUp).Row
        For b = 2 To y
        Cells(b, 3) = "=CountIf(" & a & ", B" & b & ")"
        Next b
End Sub

run the macro. It will find unique invoice numbers in col B and their counts in col C
Ravi