Solved

Countif problem by Excel VBA

Posted on 2014-07-24
6
8,200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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!

 

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

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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

634 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