Ask Question Forum:
Model Library:2025-02-08:A.I. model is online auto reply
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by hkgal
at 2024-07-24 00:16:54
ModifyPoint:500 Replies:5 POST_ID:829086USER_ID:11986
Topic:
Microsoft Excel Spreadsheet Software;VB Script;Visual Basic Programming
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
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
Expert: duncanb7 replied at 2024-07-24 02:09:41
Thanks for your points
Have a nice day
Duncan
Have a nice day
Duncan
Author: hkgal replied at 2024-07-24 01:41:47
Thanks! mate
Expert: Glenn Ray replied at 2024-07-24 01:02:38
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:
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 IdxEnd Sub 1:2:3:4:5:6:7:8:
Regards,
-Glenn
Sent from my Windows Phone
Accepted Solution
Expert: duncanb7 replied at 2024-07-24 00:59:09
500 points EXCELLENT
It should be this, Right ?
sffCount = Application.WorksheetFunction.CountIf(Range("A1:A" & LastRow), Cells(Idx, "A").Value)
Duncan
sffCount = Application.WorksheetFunction.CountIf(Range("A1:A" & LastRow), Cells(Idx, "A").Value)
Duncan
Sub countsff()Dim sffCount As LongLastRow = Range("A" & Rows.Count).End(xlUp).RowFor Idx = 2 To LastRowsffCount = Application.WorksheetFunction.CountIf(Range("A1:A" & LastRow), Cells(Idx, "A").Value)Cells(Idx, "B") = sffCountNextEnd Sub 1:2:3:4:5:6:7:8:
Expert: Steve replied at 2024-07-24 00:56:17
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...
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 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:
or drop the rows count from the line:
sffCount = Application.WorksheetFunction.CountIf(ws.Range("A:A"), ws.Cells(Idx, "A").Value) 1:
