Hi

I am using Excel 2013

I need the "Number of Customer duplicate" find out in the "E" Column in VBA

Data is in "C" Column, Result is "E" Column

I trying the code below, but not working, they have come to all return the "1" count, the correct count is in "D" Column through "=COUNTIFS($C$2:C2,C2)"

My Data is (more than 1 lakh row) long, so I apply the formula getting a long time, few data is deleted for upload purpose to this your site.

So, trying VBA "WorksheetFunction.Countifs"

Macro Code:

Sub Countifs1()
Dim lastRow As Long
Dim thisRow As Long

With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
.Cells(thisRow, "E").Value = WorksheetFunction.Countifs(ThisWorkbook.Sheets("Sheet1").Range("$C$2:C2"), ThisWorkbook.Sheets("Sheet1").Range("C2"))
Next thisRow
End With
End Sub

Thanks for Help