Solved

Countif problem by Excel VBA

Posted on 2014-07-24
6
7,226 Views
Last Modified: 2016-09-14
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
Comment
Question by:hkgal
6 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 40216179
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
 
LVL 13

Accepted Solution

by:
duncanb7 earned 500 total points
ID: 40216183
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40216188
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:hkgal
ID: 40216235
Thanks! mate
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40216267
Thanks for your points

Have a nice day

Duncan
0
 

Expert Comment

by:DJ Tubic
ID: 41798917
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now