Counta with VBA -
 

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

Counta with VBA -
 

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

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

Counta with VBA -
 

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

--
Silverbird Designs @ www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"Eduardo" wrote:

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

Counta with VBA -
 

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

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

Kevin

Counta with VBA -
 

Hi John, works like a dream thank you so much

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

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

Counta with VBA -
 

Thank you very much John, it works perfect

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

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

Counta with VBA -
 

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

"John Bundy" wrote:

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Eduardo" wrote:

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