count the no. of A

[clarksonneo]

Hi,

I want a marco that can count the number of A in column C.

My code doesn't work.
There are 2 errors
1) C4:i .......I can't use the :
2) "A"..........I can't use the " "

There are 2 difficulties:
1) The no. of row is different from day to day, so I use i = "C" & Selection.End(xlDown).ROW
2) There will be some other text in column C ,says, after cells C13. So, I use Selection.End(xlDown).

Could you please amend my code so that I can produce the result I want?

[vba]

Sub CountIf_A()

Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.End(xlDown).Offset(2, 1).Value = "No of A"

i = "C" & Selection.End(xlDown).ROW

Selection.Offset(3, 1).value = "=COUNTIF(C4:i, "A")"

End Sub

[/vba]
Thanks

[shrivallabha]

Hi,

1. Define a variable that will hold the last row number
2. Insert formula in the last row + 1 and to its offset
3. Write Count of A

like:
[VBA]Sub CountOfA()
Dim lLastRow As Long
lLastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("C" & lLastRow + 1).Formula = "=COUNTIF(C4:C" & lLastRow & ",""A"")"
Range("C" & lLastRow + 1).Offset(, -1).Value = "Count Of A"
End Sub
[/VBA]