vba. for each within for next

[montecarlo2012]

Hello.
the following code, work good as long my range is fix

Sub L_100m()
      Set rngData = Sheet1.Range("B2", "B2912")
      m = -1
               For Each cell In rngData
                        If cell = 1 Then
                                 Sheet2.Range("C2").Offset(0, m) = n
                                             n = 0
                                             m = m + 1
                                             Else
                                 n = n + 1
                        End If
               Next
End Sub

My concern now is to see the code working with a dynamic range.
the way I can explain what I am looking for is the code as a pseudocode:

Sub L_101B()

For i = 1 To 6

                     'Set rngData = Sheet1.Range("B2 to the last Row").......1
                     'Set rngData = Sheet1.Range("C2 to the last Row")........2
                     'Set rngData = Sheet1.Range("D2 to the last Row")........3
                     'Set rngData = Sheet1.Range("E2 to the last Row")........4
                     'Set rngData = Sheet1.Range("F2 to the last Row").........5
                     'Set rngData = Sheet1.Range("G2 to the last Row").........6
                     
                     m = -1
                     For Each cell In rngData
                     If cell = 2 Then
                     
                                       'Sheet3.Range("C2").Offset(0, m) = n...........1
                                       'Sheet3.Range("C18").Offset(0, m) = n..............2
                                       'Sheet3.Range("C34").Offset(0, m) = n...........3
                                       'Sheet3.Range("C50").Offset(0, m) = n.............4
                                       'Sheet3.Range("C66").Offset(0, m) = n.............5
                                       'Sheet3.Range("C82").Offset(0, m) = n.............6
                     
                     n = 0
                     m = m + 1
                     Else
                     n = n + 1
                     End If
                     Next
                     
Next i
End Sub

So the process done in: ‘Set rngData = Sheet1.Range("B2 to the last Row").......1

Will display the results on: 'Sheet3.Range("C2").Offset(0, m) = n...........1

And so on
Please. I need more than one hand here,
Thank you for reading this.

 

[rlv01]

Not tested.

Sub L_100m()
    Dim SrcWS As Worksheet, DestWS As Worksheet
    Dim rngData As Range, cell As Range, m, n
    Dim rngDest As Range, i As Long

    Set SrcWS = Sheet1
    Set DestWS = Sheet2

    Set rngDest = DestWS.Range("C2")

    For i = 0 To 5
        Set rngData = SrcWS.Range(SrcWS.Cells(2, 2 + i), SrcWS.Cells(SrcWS.Rows.Count, 2 + i).End(xlUp))
        m = -1
        For Each cell In rngData
            If cell = 2 Then
                rngDest.Offset(0, m) = n
                n = 0
                m = m + 1
            Else
                n = n + 1
            End If
        Next cell
        Set rngDest = rngDest.Offset(16)
    Next i
End Sub

 

[montecarlo2012]

Thanks Sir. rlv01.
Not tested, fine
When I tested do not work as expected.
I expect:

and what I got now from your code is:

thanks for your respond.

 

[rlv01]

Keep in mind that without input data to test, whether or not you get '9 8 4 2' or '9 8 4 2 15 7 0 19 is meaningless to me. I noticed that in your first example showing the code you use now, you have:

                     For Each cell In rngData
                     If cell = 1 Then

but in your second example for code you want, you changed it to

                     For Each cell In rngData
                     If cell = 2 Then

I assumed you had your reasons for changing it, so that's what my example uses too. Maybe that has something to do with it? But I'm just guessing w/o data to test against.

 

[montecarlo2012]

Thank you Sir, your observation make me realize I skip some info, reason why do not work on the first place,
now is working,
so I checked as solution.
Can you please are able to break down a little the logic you use,
thank you, this is more than a code for me, is a lesson.

 

[rlv01]

Here are some additional comments.

Sub L_100m()
'Declare variables. Use of variables helps with code clarity
'Declaring all variables is good programming practice and helps to troubleshoot code
    Dim SrcWS As Worksheet, DestWS As Worksheet
    Dim rngData As Range, cell As Range, m As Long, n As Long
    Dim rngDest As Range, i As Long
    Dim Msg As String

    'Assign worksheets to worksheet variables.
    Set SrcWS = Sheet1                                'Sheet with source data
    Set DestWS = Sheet4                               'Sheet for macro output

    'Set first destination range as per
    'Sheet3.Range("C2").Offset(0, m) = n...........1
    Set rngDest = DestWS.Range("C2")

    For i = 0 To 5                                    'increment from columns B to G
        'Set source data range as per:
        'Set rngData = Sheet1.Range("B2 to the last Row").......1
        Set rngData = SrcWS.Range(SrcWS.Cells(2, 2 + i), SrcWS.Cells(SrcWS.Rows.Count, 2 + i).End(xlUp))    ' set range for the data column


        Msg = "For i = " & i & ",  the data range is: " & rngData.Address(False, False, , True)    'debug message
        MsgBox Msg, vbOKOnly Or vbInformation         'debug message

        m = -1
        For Each cell In rngData
            If cell = 2 Then
                rngDest.Offset(0, m) = n              'put results in output sheet
                n = 0
                m = m + 1
            Else
                n = n + 1
            End If
        Next cell
        Set rngDest = rngDest.Offset(16)              'Increment to the next destination row, i.e. C2 to C18,C34,C50,C66, C82
    Next i
End Sub

 

[montecarlo2012]

rlv01 Thank you so much, very nice to do this.