Counta with VBA -

[Guest]

I have in column A information, some of the rows contain the word "PO1"
What I need in VBA to do is to count the "PO1" and put the total below the
last row used but in column B
I need it
Thank you in advance
Eduardo

 

[Guest]

here you go

Sub main()
Dim myCounter As Long
Dim lastRow As Long

'find last used cell
lastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

myCounter = 0

For i = 1 To lastRow ' set i = to the first row you want to search in
If Sheets("sheet1").Cells(i, 1) = "PO1" Then myCounter = myCounter + 1

Next

Sheets("sheet1").Cells(i, "B") = myCounter
End Sub

 

[Guest]

If I understand you correctly, you should be able to accomplish this goal
without using a VBA code. Correct me if I am wrong, but you want to place in
a cell in column B the number of times "PO1" appears in column A.

In cell B1 place this code.

=SUM(A1:A100,IF(A1:A100="PO1",1,0))

This is an array formula so it requires that you you enclose the formula in
braces {}

Click the cell that contains the array formula (array formula: A formula
that performs multiple calculations on one or more sets of values, and then
returns either a single result or multiple results. Array formulas are
enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).

That should do it.

~Ryan

 

[Guest]

Place this formula in cell the last row of column B:

=COUNTIF(A:A,"*P01*")

Kevin

 

[Guest]

Hi John, works like a dream thank you so much

here you go

Sub main()
Dim myCounter As Long
Dim lastRow As Long

'find last used cell
lastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

myCounter = 0

For i = 1 To lastRow ' set i = to the first row you want to search in
If Sheets("sheet1").Cells(i, 1) = "PO1" Then myCounter = myCounter + 1

Next

Sheets("sheet1").Cells(i, "B") = myCounter
End Sub

 

[Guest]

Thank you very much John, it works perfect

here you go

Sub main()
Dim myCounter As Long
Dim lastRow As Long

'find last used cell
lastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

myCounter = 0

For i = 1 To lastRow ' set i = to the first row you want to search in
If Sheets("sheet1").Cells(i, 1) = "PO1" Then myCounter = myCounter + 1

Next

Sheets("sheet1").Cells(i, "B") = myCounter
End Sub

 

[Guest]

John, thank you so much for your help it works great