Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8928
  • Last Modified:

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
0
hkgal
Asked:
hkgal
1 Solution
 
SteveCommented:
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 

Open in new window


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

Open in new window

0
 
duncanb7Commented:
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

Open in new window

0
 
Glenn RayExcel 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

Open in new window


Regards,
-Glenn
Sent from my Windows Phone
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
hkgalAuthor Commented:
Thanks! mate
0
 
duncanb7Commented:
Thanks for your points

Have a nice day

Duncan
0
 
DJ TubicCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now