Nested loop Programing Challenge

[Benjamin Fortunato]

I would like someone to explain to me how nested loops work. There does not
seem to be a lot of literature on it. I am trying to go through a worksheet
and count the number of ones. I then have to take these vales and create a
list which tallies the numeric cell value of that item. See the example below
it will clarify everything. I am using nested loops and my list generates the
same number. I believe what it is doing is taking the last value and filling
it into each cell.



Worksheet(2)

0 0 0 0 0
0 1 1 0 0
0 0 0 0 1
1 0 0 0 0
0 0 0 0 1

Worksheet(3)

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Worksheet(4)

7
8
15
16
25


But I get

25
25
25
25
25


Here is the code


Worksheets(2).Activate

Set totalRange = Range("A1").Resize(RowNum, ClmNum)

OneCount = Application.WorksheetFunction.CountIf(totalRange, 1)

ReDim listOne(OneCount + 1)



i = 0

For rCnt = 1 To RowNum
For ClmCnt = 1 To ClmNum
If Worksheets(2).Cells(rCnt, ClmCnt).Value = 1 Then
listOne(i) = Worksheets(3).Cells(rCnt, ClmCnt)
i = i + 1
End If
Next
Next


Thanks!

 

[Bob Phillips]

I do not know why you dimension the array +1 but it holds what you say it
should. Are you dropping it onto a sheet, that could be the problem?

 

[Per Jessen]

Hi

Your nested loops are fine, I thing problem how you 'read' the values from
your array variable.

Try this:

Sub test()
Dim listOne()
RowNum = 5
ClmNum = 5
Worksheets("Sheet2").Activate
Set totalRange = Range("A1").Resize(RowNum, ClmNum)
OneCount = Application.WorksheetFunction.CountIf(totalRange, 1)
ReDim listOne(OneCount - 1)
i = 0

For rCnt = 1 To RowNum
For ClmCnt = 1 To ClmNum
If Worksheets(2).Cells(rCnt, ClmCnt).Value = 1 Then
listOne(i) = Worksheets(3).Cells(rCnt, ClmCnt)
i = i + 1
End If
Next
Next
For c = LBound(listOne) To UBound(listOne)
Worksheets("Sheet4").Range("A1").Offset(off, 0) = listOne(c)
off = off + 1
Next
End Sub

Regards,
Per

 

[Mike H]

Hi,

This question isn't clear, at least to me it isn't

I am trying to go through a worksheet
and count the number of ones. I then have to take these values and create a
list which tallies the numeric cell value of that item

So if we find 10 ones in a sheets does that mean the answer your looking for
is 10?

Also your code

Set totalRange = Range("A1").Resize(RowNum, ClmNum)

Where are RowNum & ClmNum coming from?

Lastly I assume this is the answer you want

7
8
15
16
25

Why? What is the reasoning for those answers?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.