PDA

View Full Version : Loop through the range to find the duplicates and print the number of occurrences



rkulasekaran
11-18-2015, 10:19 PM
Hello all,

I have a code(formula) to find the duplicates in column F and print the number of occurrences(1,2...) in column AU.
The code is below,



Dim C_S Range
Set C_Range = C_S.Sheet.Range("F5:F" & C_S.Sheet.Range("F" & Rows.Count).End(xlUp).Row)
Dim q As Integer
q=5
For Each C_Range In C_S.Sheet.Range("F5:F" & C_S.Sheet.Range("F" & Rows.Count).End(xlUp).Row)
C_S.Sheet.Cells(q, 47).Value = "=(COUNTIF($F$5:F5,F5))"
Next


The above code find the value and prints only in the first cell(5,47) and it it is pulled down the values are filled. I tried by adding an integer and try to increment it but the same value is printed in the column AU till the last cell. I would like it to be filled automatically in the column when the For loop runs.
kindly do anyone suggest some way. Thanks in advance.

mancubus
11-19-2015, 02:35 AM
Sub vbax_54324_Insert_Formula_To_Column()
With Worksheets("MySheet") 'change MySheet to suit
.Range("AU5:AU" & .Range("F" & .Rows.Count).End(xlUp).Row).Formula = "=COUNTIF($F$5:F5,F5)"
End With
End Sub