vba. for each within for next

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
Hello.
the following code, work good as long my range is fix

VBA Code:
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:


VBA Code:
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,077
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not tested.

VBA Code:
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
 
Solution

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
Thanks Sir. rlv01.
Not tested, fine
When I tested do not work as expected.
I expect:
1621769416528.png

1621769455034.png

and what I got now from your code is:
1621769548144.png


thanks for your respond.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,077
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

VBA Code:
                     For Each cell In rngData
                     If cell = 1 Then

but in your second example for code you want, you changed it to
VBA Code:
                     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

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
1,077
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Here are some additional comments.

VBA Code:
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

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
rlv01 Thank you so much, very nice to do this.
 

Forum statistics

Threads
1,144,281
Messages
5,723,473
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top