loops and count
 

Hi The 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

loops and count
 

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

http://www.vonixx.com


"G" wrote:

Hi The 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

loops and count
 

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 The 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

loops and count
 

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
...
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 The 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

loops and count
 

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
...
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 The 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

loops and count
 

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" wrote in message
...
Hi The 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

loops and count
 

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" wrote in message
...
Hi The 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

loops and count
 

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" wrote in message
...
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" wrote in message
...
Hi The 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