Solved

Countif problem by Excel VBA

Posted on 2014-07-24
6
7,554 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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