Solved

Countif problem by Excel VBA

Posted on 2014-07-24
6
6,966 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Closing Comment

by:hkgal
Comment Utility
Thanks! mate
0
 
LVL 13

Expert Comment

by:duncanb7
Comment Utility
Thanks for your points

Have a nice day

Duncan
0
 

Expert Comment

by:DJ Tubic
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

6 Experts available now in Live!

Get 1:1 Help Now