• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

WorksheetFunction.Countifs not Work VBA

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:

>>> use code - tags <<<

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
 

Attachments

  • WorksheetFunctionCountifs Work New.xlsm
    36.2 KB · Views: 5
Last edited by a moderator:
sivaprakasam
... hmm?
Did You change something?
Do You know what are Your expected results?
Did You read my hint?
You'e using always range from C2 to C2 and You're compare always cells C2 -value.
Of course, it will give same result all the time.
 
thq for help...

i have try another method that ".Cells(thisRow, "E").Value = WorksheetFunction.Countifs(.Range("$C$2:C" & thisRow), .Range("C" & thisRow))"

it is work...
 
Back
Top