CODEWITHSUNDEEP
excelif-statementfor-loopvba
Isra Shaikh
Isra Shaikh

Reputation: 151

For loop incomplete

Im getting an error that my next is without a for :/ What am I missing here. I want it to check column C (cells(y,3)) and if its blank, then look at column A, find the matching value in G and then give the corresponding value in F in column C. And if it's not blank, then move onto the next cell vertically.

    Dim x As Double
    Dim y As Double
    For y = 2 To 84212
        For x = 531 To 632

            If IsEmpty(Cells(y, 3)) Then
                If Cells(y, 1).Value = Cells(x, 6).Value Then Cells(y, 3).Value = Cells(x,7).Value
            Else: Next x
            Else: Next y
End Sub

Upvotes: 0

Views: 164

Answers (2)

YowE3K
YowE3K

Reputation: 23974

Your loops and If statements should look something like:

Dim x As Long ' Use Long, rather than Double for integer arithmetic
Dim y As Long
For y = 2 To 84212
    If IsEmpty(Cells(y, 3)) Then ' Perform this test before starting the 
                                 ' inner loop, thus saving 102 iterations
        For x = 531 To 632
            If Cells(y, 1).Value = Cells(x, 6).Value Then
                Cells(y, 3).Value = Cells(x, 7).Value
                Exit For ' No use looking at other "x" rows once a match has been found
            End If
        Next x
    End If
Next y

Also notice how indenting code allows you to correctly ensure If statements are matched with End If, and For statements are matched with Next statements. Apart from ensuring your code is valid, it also will make it easier to read. (Notice that I tried editing your question to indent code [something we often do to make it easier for other people who are trying to answer your question] and none of the statements line up - I ended up giving up because of the two Else statements with only one Block If to match them to.)

Upvotes: 1

OES
OES

Reputation: 321

You have started two FOR loops and did not end any of them. You just put them in Else statement instead of ending IF:

Dim x As Double
Dim y As Double
For y = 2 To 84212
For x = 531 To 632

If IsEmpty(Cells(y, 3)) Then
If Cells(y, 1).Value = Cells(x, 6).Value Then Cells(y, 3).Value = Cells(x,7).Value Then
'Do something
End if
End if

Next x ' End loop with x variable
Next y ' End loop with y variable
       ' Both x and y can be omitted. This is just for clarifications.
End Sub

Upvotes: 0

Related Questions