[Solved] Array Count help (newbie)

[zaoth]

Hi,

I have an array of 300 respondents and their answers to 5 questions.

Basically what I want to do is output next to the respondent number how many times they said X across all questions, for example Person 1, 2. Person 2, 4.
This is what my array looks like so far, I have it being output just as a check that it works.

Dim Arr() As Variant
Dim wstd As Worksheet
Dim wstska As Worksheet
Set wstd = Sheets("td")
Set wstska = Sheets("taska")
Arr = wstd.Range("A1:G301")
wstska.Range("a1:g301") = Arr

A countif is probably needed or something along those lines with a loop. I have no idea what to do next. I have looked up countif's and they just use the formula but I would like to do it in the VBA code.

Here is a Sample: sample.xlsm

Thanks.

[Vikas_Gautam]

provide a sample workbook....
That will help us to help you better...




Don't forget to click *

[zaoth]

provide a sample workbook....
That will help us to help you better...




Don't forget to click *

Added a sample now. Thanks for the quick reply.

[Arkadi]

Suggestion....

dim these two to use as one dimensional array of x per user, and counter for a loop:

Dim arrcount() As Variant
Dim counter As Long

then loop through the users to store their "X"'s:

ReDim arrcount(1 To UBound(Arr, 1)) 'this is to set up your array of responses with the number of records in your first array
For i = 1 To UBound(Arr, 1) 'loop thrhough number of respondents
    counter = 0 'counter that starts at zero each time, will increment when response matches what you are looking for
    For j = 2 To 7 'to check the cells in columns B to G as per your defined range
        If wstd.Cells(i, j).Value = "X" Then counter = counter + 1 'increment counter if answer matches criteria ("X")
    Next j
    arrcount(i) = counter 'stores number of X's for user "i"
Next i

What you do with that info afterwards is up to you, but you can loop through users with another for loop....

for k = 1 to ubound(arrcount) 
     do stuff
next k

[zaoth]

Suggestion....

dim these two to use as one dimensional array of x per user, and counter for a loop:

Dim arrcount() As Variant
Dim counter As Long

then loop through the users to store their "X"'s:

ReDim arrcount(1 To UBound(Arr, 1)) 'this is to set up your array of responses with the number of records in your first array
For i = 1 To UBound(Arr, 1) 'loop thrhough number of respondents
    counter = 0 'counter that starts at zero each time, will increment when response matches what you are looking for
    For j = 2 To 7 'to check the cells in columns B to G as per your defined range
        If wstd.Cells(i, j).Value = "X" Then counter = counter + 1 'increment counter if answer matches criteria ("X")
    Next j
    arrcount(i) = counter 'stores number of X's for user "i"
Next i

What you do with that info afterwards is up to you, but you can loop through users with another for loop....

for k = 1 to ubound(arrcount) 
     do stuff
next k

Thanks for the help. Had to tweak it a bit to get it working properly but your guidance helped!

[Arkadi]

Yeah didn't have your actual sheet to work with, but figured you'd have enough there to work with
Glad it worked out for you, nicely done.

Edit OOOPS! missed you did put in a sample sheet