# Countif problem by Excel VBA

Hello! I tried to do a countif function by VBA in Excel, by excel it should be like = countif("A:A",A2) .... like this and map the count to Column Z.

However my code below return all zero value......where am I get wrong? pls help....thanks.

Sub countsff()

Dim sffCount As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Idx = 2 To LastRow

sffCount = Application.WorksheetFunction.CountIf(Range("A" & Rows.Count), Cells(Idx, "A").Value)

Cells(Idx, "Z") = sffCount

Next

End Sub
###### Who is Participating?

Commented:
It should be this, Right ?

sffCount = Application.WorksheetFunction.CountIf(Range("A1:A" & LastRow), Cells(Idx, "A").Value)

Duncan

``````Sub countsff()
Dim sffCount As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Idx = 2 To LastRow
sffCount = Application.WorksheetFunction.CountIf(Range("A1:A" & LastRow), Cells(Idx, "A").Value)
Cells(Idx, "B") = sffCount
Next
End Sub
``````
0

Commented:
Are you on the right sheet?
It may be good practice to Dim and use the sheet:
And the range needs to have a from A1 added.
See below...

``````Sub countsff()

Dim sffCount As Long
Dim ws as worksheet
Set ws = sheets("Sheet1")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For Idx = 2 To LastRow

sffCount = Application.WorksheetFunction.CountIf(ws.Range("A1:A" & ws.Rows.Count), ws.Cells(Idx, "A").Value)

ws.Cells(Idx, "Z") = sffCount

Next

End Sub
``````

or drop the rows count from the line:
`````` sffCount = Application.WorksheetFunction.CountIf(ws.Range("A:A"), ws.Cells(Idx, "A").Value)
``````
0

Excel VBA DeveloperCommented:
You appear to only be checking the last cell in column A against all values in A. I would have expected the last value in column Z to be 1, however.

Try this revision to your code:
``````Sub countsff()
Dim sffCount As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Idx = 2 To LastRow
sffCount = Application.WorksheetFunction.CountIf("A:A", Cells(Idx, "A").Value)
Cells(Idx, "Z") = sffCount
Next Idx
End Sub
``````

Regards,
-Glenn
Sent from my Windows Phone
0

Author Commented:
Thanks! mate
0

Commented:

Have a nice day

Duncan
0

Commented:
Is it possible in column "Z" to have sequential numbering of duplicates.  Instead of putting 2 on both lines that are duplicated, it will put 1 on the first duplicate row and 2 on the second duplicated row?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.