CODEWITHSUNDEEP
vbaloopsgoto
UserX
UserX

Reputation: 159

Loop Help and Goto

I have 3 worksheets. The first 2 have i convert into two arrays (array1 & array2), and then do calculations between them to create the third.

The Macro I created utilizes this piece of code ---

Z = 1

For x = 1 To UBound(array1, 1)
    For y = 1 To UBound(array2, 1)
        If array1(x, 4) = 0 Then
            GoTo Line1
        End If
        If array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3)Then
            If array1(x, 4) > array2(y, 5) Then
                array3(z, 1) = array1(x, 3)
            ElseIf array1(x, 4) = array2(y, 5) Or array1(x, 4) < array2(y, 5) Then
                array3(z, 1) = array1(x, 3)
            End If
            z = z + 1
        End If
    Next y
Line1:
Next x

It takes a piece of array1 and loops it through array2 and creates a result in array3

Basically when array1(x, 4) = 0, I need it to move on to the next X. I can't figure out how to loop this without the GoTo Line1.

If i move it down, then it will continue to loop through array2(y), instead of moving on to next X. If i move it above, then y resets and it runs through the For y loop again

Also is using GoTo Line X, bad practice in VBA. Should I always try to avoid using it. I am fairly new to it.

Upvotes: 0

Views: 157

Answers (1)

Tomalak
Tomalak

Reputation: 338178

Writing code that relies on GoTo is widely considered bad style.

VB(A) has a few built-in constructs that are used for its way of error handling that need GoTo. Those are unavoidable. All the other ones should be avoided.

In this case it's fairly easy, you can break a For loop with Exit For:

Z = 1

For x = 1 To UBound(array1, 1)
    For y = 1 To UBound(array2, 1)
        If array1(x, 4) = 0 Then Exit For
        If And array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3) Then
            If array1(x, 4) > array2(y, 5) Then
                array3(z, 1) = array1(x, 3)
            ElseIf array1(x, 4) = array2(y, 5) Or array1(x, 4) < array2(y, 5) Then
                array3(z, 1) = array1(x, 3)
            End If
            z = z + 1            
        End If
    Next y
Next x

Alternative (has one nesting level more):

For x = 1 To UBound(array1, 1)
    If array1(x, 4) <> 0 Then
        For y = 1 To UBound(array2, 1)
            If And array1(x, 1) = array2(y, 1) And array1(x, 2) = array2(y, 3) Then
                If array1(x, 4) > array2(y, 5) Then
                    array3(z, 1) = array1(x, 3)
                ElseIf array1(x, 4) = array2(y, 5) Or array1(x, 4) < array2(y, 5) Then
                    array3(z, 1) = array1(x, 3)
                End If
                z = z + 1            
            End If
        End If
    Next y
Next x

Upvotes: 1

Related Questions