Why does my For Each loop only iterate once?

Question

I need my for each loop to iterate through the entire range. I have three for each loops with If-statements that checks if a condition is being met. However, the loop only iterates once even if the condition is being met or not. The sub then breaks.

Set ServiceRNG = Dataworksheet.Range("U2").End(xlDown)
Set OldServiceNamesRNG1 = ProductOderingCodeWS.Range("B7").End(xlToRight)
Set OldServiceNamesRNG2 = ProductOderingCodeWS.Range("B8").End(xlToRight)
CounterSheet1 = 2
Set Nextcellvalue = Dataworksheet.Range("U" & CounterSheet1 + 1)
Set Productorderingcode = Dataworksheet.Range("U" & CounterSheet1).Offset(0, 5)

For Each ServiceName In ServiceRNG
    Nextcellvalue = Dataworksheet.Range("U" & CounterSheet1 + 1)
    If ServiceName.Value = Nextcellvalue.Value Then
        Productorderingcode = Dataworksheet.Range("U" & CounterSheet1).Offset(0, 5)
        Productorderingcode.Copy


    CounterDatabaseWS = 2
    For Each OldServiceName1 In OldServiceNamesRNG1
        If ServiceName.Value = OldServiceName1.Value Then
        ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Exit For
        End If
        CounterDatabaseWS = CounterDatabaseWS + 1
    Next OldServiceName1

    CounterDatabaseWS = 2
    For Each OldServiceNames2 In OldServiceNamesRNG2
        If ServiceName.Value = OldServiceNames2.Value Then
        ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Exit For
        End If
        CounterDatabaseWS = CounterDatabaseWS + 1
    Next OldServiceNames2

Else
    Productorderingcode = Dataworksheet.Range("U" & CounterDatabaseWS).Offset(0, 5).Value
    Productorderingcode.Copy
    ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End If

CounterSheet1 = CounterSheet1 + 1
Next ServiceName

The code runs as it should, the evaluation of conditions start at the first index of the ranges, but does not iterate through the entire range. In this case, ServiceRNG, OldeServiceNamesRNG1 and OldeServiceNamesRNG2.

Have you tried stepping (F8) through the code to 'watch' the process? — CLR
– CLR, Commented Sep 5, 2019 at 9:41

Louis · Accepted Answer · 2019-09-05 10:54:18Z

3

Your For Each loop iterates only one time because the line of code Set ServiceRNG = Dataworksheet.Range("U2").End(xlDown) simply get the last Range cell it founds, starting from the cell U2 and not all of them.

If you want to pick all the values it founds, change that line from this:

Set ServiceRNG = Dataworksheet.Range("U2").End(xlDown)

To this:

Set ServiceRNG = Dataworksheet.Range(Dataworksheet.Range("U2"), Dataworksheet.Range("U2").End(xlDown))

This will make the For Each loop iterating to all the values.

Of course, you have to do the same operation with all the variables that are involved in For Each loops.

Hope this helps.

edited Sep 5, 2019 at 10:54

answered Sep 5, 2019 at 9:50

Louis

3,6222 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

simple-solution · Accepted Answer · 2019-09-05 10:21:55Z

These lines give back only one cell!

Set ServiceRNG = Sheets(1).Range("U2").End(xlDown)
Set OldServiceNamesRNG1 = Sheets(1).Range("B7").End(xlToRight)
Set OldServiceNamesRNG2 = Sheets(1).Range("B8").End(xlToRight)

see: selection of multiple columns with end(xlDown).End(xlUp).Row

or see: https://www.excel-easy.com/vba/examples/from-active-cell-to-last-entry.html

You need to change it to something like:

Set ServiceRNG = ws1.Range(ws1.Range("U2"), ws1.Range("U2").End(xlDown))
Set OldServiceNamesRNG1 = ws1.Range(ws1.Range("B7"), ws1.Range("B7").End(xlToRight))
Set OldServiceNamesRNG2 = ws1.Range(ws1.Range("B8"), ws1.Range("B8").End(xlToRight))

And your if clause "If ServiceName.Value = Nextcellvalue.Value Then" ends AFTER the 2nd and the 3rd loop. Is this intentional?

Here a working MVE based on your code:

Option Explicit

Sub test3Loops()

Dim ServiceRNG As Range
Dim OldServiceNamesRNG1 As Range
Dim OldServiceName1 As Range
Dim OldServiceNamesRNG2 As Range
Dim OldServiceNames2 As Range

Dim Nextcellvalue As Range
Dim Productorderingcode As Range
Dim ServiceName As Range

Dim CounterSheet1 As Integer
Dim CounterSheet2 As Integer
Dim Dataworksheet As Worksheet
Dim ProductOderingCodeWS As Worksheet
Dim ws1 As Worksheet

Set ws1 = Sheets(1)
'Set ProductOderingCodeWS = Sheets(2)

'Setup Data for ws1

'ServiceRNG
ws1.Range("U1") = "ServiceRNG"
ws1.Range("U2") = "A"
ws1.Range("U3") = "B"
ws1.Range("U4") = "C"

'OldServiceNamesRNG1
ws1.Range("B7") = "1"
ws1.Range("C7") = "2"
ws1.Range("D7") = "3"

'OldServiceNamesRNG2
ws1.Range("B8") = "X"
ws1.Range("C8") = "Y"
ws1.Range("D8") = "Z"

'ERROR: These lines give back only one cell!
'Set ServiceRNG = Sheets(1).Range("U2").End(xlDown)
'Set OldServiceNamesRNG1 = Sheets(1).Range("B7").End(xlToRight)
'Set OldServiceNamesRNG2 = Sheets(1).Range("B8").End(xlToRight)
'see: https://stackoverflow.com/questions/50370919/selection-of-multiple-columns-with-endxldown-endxlup-row
'see: https://www.excel-easy.com/vba/examples/from-active-cell-to-last-entry.html

Set ServiceRNG = ws1.Range(ws1.Range("U2"), ws1.Range("U2").End(xlDown))
Set OldServiceNamesRNG1 = ws1.Range(ws1.Range("B7"), ws1.Range("B7").End(xlToRight))
Set OldServiceNamesRNG2 = ws1.Range(ws1.Range("B8"), ws1.Range("B8").End(xlToRight))

CounterSheet1 = 2

'Set Nextcellvalue = Dataworksheet.Range("U" & CounterSheet1 + 1)
'Set Productorderingcode = Dataworksheet.Range("U" & CounterSheet1).Offset(0, 5)

For Each ServiceName In ServiceRNG
    Set Nextcellvalue = ws1.Range("U" & CounterSheet1 + 1)

'        If ServiceName.Value = Nextcellvalue.Value Then
'            Productorderingcode = Dataworksheet.Range("U" & CounterSheet1).Offset(0, 5)
'            Productorderingcode.Copy
'        'missing end if? You did include the 2nd and 3rd loop into the above if clause!
'        End If

    'CounterDatabaseWS = 2
    For Each OldServiceName1 In OldServiceNamesRNG1
'        If ServiceName.Value = OldServiceName1.Value Then
'            ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'        Exit For
'        End If
        Debug.Print "OldServiceName1 : "; ServiceName.Value & " - " & OldServiceName1.Value
    Next OldServiceName1

    'CounterDatabaseWS = 2
    For Each OldServiceNames2 In OldServiceNamesRNG2
'        If ServiceName.Value = OldServiceNames2.Value Then
'        ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'        Exit For
'        End If
        Debug.Print "OldServiceName2 : "; ServiceName.Value & " - " & OldServiceNames2.Value
    Next OldServiceNames2

    'Else
    '    Productorderingcode = Dataworksheet.Range("U" & CounterDatabaseWS).Offset(0, 5).Value
    '    Productorderingcode.Copy
    '    ProductOderingCodeWS.Cells(Rows.Count, CounterDatabaseWS).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    'End If

'CounterSheet1 = CounterSheet1 + 1
Next ServiceName

End Sub

The Immediate window finally shows:

OldServiceName1 : A - 1
OldServiceName1 : A - 2
OldServiceName1 : A - 3
OldServiceName2 : A - X
OldServiceName2 : A - Y
OldServiceName2 : A - Z
OldServiceName1 : B - 1
OldServiceName1 : B - 2
OldServiceName1 : B - 3
OldServiceName2 : B - X
OldServiceName2 : B - Y
OldServiceName2 : B - Z
OldServiceName1 : C - 1
OldServiceName1 : C - 2
OldServiceName1 : C - 3
OldServiceName2 : C - X
OldServiceName2 : C - Y
OldServiceName2 : C - Z

Collectives™ on Stack Overflow

Why does my For Each loop only iterate once?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related