[SOLVED] loops and count

[G]

Hi There: Any assistance will be highly appreciated. I am trying to loop
through a 2D array where it goes through each row. and counts the Xs
corresponding to the value. For example, Ideally 10 should have a count of 2
x's, 14 a cnt of 1 x , 12 0 etc..
10 X
12
14 X
13
10 X
10
Thanks in advance
G

[Edwin Tam]

You may try the macros below:

You can change the value in subroutine “go_count� so that the macro looks
for different values.

Sub go_count()
MsgBox xcount(20)
End Sub

Function xcount(ByVal x)
xcount = 0
Dim cell As Object
For Each cell In Selection.Columns(1).Cells
If cell.Value = x Then
If UCase(cell.Offset(0, 1).Value) = "X" Then
xcount = xcount + 1
End If
End If
Next
End Function

Regards,
Edwin Tam
edwintam@vonixx.com
http://www.vonixx.com


"G" wrote:

> Hi There: Any assistance will be highly appreciated. I am trying to loop
> through a 2D array where it goes through each row. and counts the Xs
> corresponding to the value. For example, Ideally 10 should have a count of 2
> x's, 14 a cnt of 1 x , 12 0 etc..
> 10 X
> 12
> 14 X
> 13
> 10 X
> 10
> Thanks in advance
> G

[Rich]

You could use SUMPRODUCT like this

=SUMPRODUCT(--(A1:A6=D1),--(B1:B6="X"))

in cell D1 , insert the number you want to do a count on. Incorporating this
into your spreadsheet may be a better option than VBA.

Rich

"G" wrote:

> Hi There: Any assistance will be highly appreciated. I am trying to loop
> through a 2D array where it goes through each row. and counts the Xs
> corresponding to the value. For example, Ideally 10 should have a count of 2
> x's, 14 a cnt of 1 x , 12 0 etc..
> 10 X
> 12
> 14 X
> 13
> 10 X
> 10
> Thanks in advance
> G

[Bob Phillips]

You could also use SUMPRODUCT in VBA

evaluate("SUMPRODUCT(--(A1:A6=D1),--(B1:B6=""X""))")

but do you mean a worksheet range, or are you referring to a VBA array?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rich" <rich@hotmail> wrote in message
news:80A97C11-21D6-4953-828E-36747E97A99C@microsoft.com...
> You could use SUMPRODUCT like this
>
> =SUMPRODUCT(--(A1:A6=D1),--(B1:B6="X"))
>
> in cell D1 , insert the number you want to do a count on. Incorporating
this
> into your spreadsheet may be a better option than VBA.
>
> Rich
>
> "G" wrote:
>
> > Hi There: Any assistance will be highly appreciated. I am trying to loop
> > through a 2D array where it goes through each row. and counts the Xs
> > corresponding to the value. For example, Ideally 10 should have a count
of 2
> > x's, 14 a cnt of 1 x , 12 0 etc..
> > 10 X
> > 12
> > 14 X
> > 13
> > 10 X
> > 10
> > Thanks in advance
> > G

[G]

Hi thanks for all your help, I mean a VBA array, because it will be looking
for the value X in different cells, going down each row.



"Bob Phillips" wrote:

> You could also use SUMPRODUCT in VBA
>
> evaluate("SUMPRODUCT(--(A1:A6=D1),--(B1:B6=""X""))")
>
> but do you mean a worksheet range, or are you referring to a VBA array?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Rich" <rich@hotmail> wrote in message
> news:80A97C11-21D6-4953-828E-36747E97A99C@microsoft.com...
> > You could use SUMPRODUCT like this
> >
> > =SUMPRODUCT(--(A1:A6=D1),--(B1:B6="X"))
> >
> > in cell D1 , insert the number you want to do a count on. Incorporating
> this
> > into your spreadsheet may be a better option than VBA.
> >
> > Rich
> >
> > "G" wrote:
> >
> > > Hi There: Any assistance will be highly appreciated. I am trying to loop
> > > through a 2D array where it goes through each row. and counts the Xs
> > > corresponding to the value. For example, Ideally 10 should have a count
> of 2
> > > x's, 14 a cnt of 1 x , 12 0 etc..
> > > 10 X
> > > 12
> > > 14 X
> > > 13
> > > 10 X
> > > 10
> > > Thanks in advance
> > > G
>
>
>

[Tom Ogilvy]

when you say a 2D array, is this a vba array in memory or are you talking
about a range of cells on a worksheet?

--
Regards,
Tom Ogilvy


"G" <G@discussions.microsoft.com> wrote in message
news:3985D87E-ABA0-4EF1-A377-1FFE7299D612@microsoft.com...
> Hi There: Any assistance will be highly appreciated. I am trying to loop
> through a 2D array where it goes through each row. and counts the Xs
> corresponding to the value. For example, Ideally 10 should have a count of
2
> x's, 14 a cnt of 1 x , 12 0 etc..
> 10 X
> 12
> 14 X
> 13
> 10 X
> 10
> Thanks in advance
> G

[G]

Range of cells on a worksheet.

"Tom Ogilvy" wrote:

> when you say a 2D array, is this a vba array in memory or are you talking
> about a range of cells on a worksheet?
>
> --
> Regards,
> Tom Ogilvy
>
>
> "G" <G@discussions.microsoft.com> wrote in message
> news:3985D87E-ABA0-4EF1-A377-1FFE7299D612@microsoft.com...
> > Hi There: Any assistance will be highly appreciated. I am trying to loop
> > through a 2D array where it goes through each row. and counts the Xs
> > corresponding to the value. For example, Ideally 10 should have a count of
> 2
> > x's, 14 a cnt of 1 x , 12 0 etc..
> > 10 X
> > 12
> > 14 X
> > 13
> > 10 X
> > 10
> > Thanks in advance
> > G
>
>
>

[Tom Ogilvy]

You really don't need code to do this

assume your data is in column A and B
in rows 1 to 1000

=Sumproduct(--($A$1:$A$1000=14),--($B$1:$B$1000="X"))

You can replace 1 with a cell reference. So say in E1 to E10 you put in
the numbers 10 to 20

in F1 you would put
=Sumproduct(--($A$1:$A$1000=E1),--($B$1:$B$1000="X"))

then drag fill down to F10.

--
Regards,
Tom Ogilvy


"G" <G@discussions.microsoft.com> wrote in message
news:BBB4F787-581C-4073-87B7-14FAE2D81033@microsoft.com...
> Range of cells on a worksheet.
>
> "Tom Ogilvy" wrote:
>
> > when you say a 2D array, is this a vba array in memory or are you
talking
> > about a range of cells on a worksheet?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "G" <G@discussions.microsoft.com> wrote in message
> > news:3985D87E-ABA0-4EF1-A377-1FFE7299D612@microsoft.com...
> > > Hi There: Any assistance will be highly appreciated. I am trying to
loop
> > > through a 2D array where it goes through each row. and counts the Xs
> > > corresponding to the value. For example, Ideally 10 should have a
count of
> > 2
> > > x's, 14 a cnt of 1 x , 12 0 etc..
> > > 10 X
> > > 12
> > > 14 X
> > > 13
> > > 10 X
> > > 10
> > > Thanks in advance
> > > G
> >
> >
> >