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

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

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

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

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

Kevin

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

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

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