loops and count

[Guest]

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

 

[Guest]

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
(e-mail address removed)
http://www.vonixx.com

 

[Guest]

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

 

[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)

 

[Guest]

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.

 

[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?

 

[Guest]

Range of cells on a worksheet.

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?

 

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