VBA in Excel 2007 won't execute nested for loops

Question

I've written nested For loops and even though the conditions are met it is not executing the code of the For loops. I tried commenting out the outermost For loop but the inner loop doesn't work either. I'm working in Excel 2007

Sub CalcAll()

Dim a As Integer
a = 10
Dim b As Integer
b = 10

For a = 10 To a = (Range("B" & Rows.Count).End(xlUp).Row) Step 1

    For b = 10 To b = (Worksheets("DistanceLookupTable").Cells(2, Sheet1.Columns.Count).End(xlToLeft).Column) Step 1

        If IsEmpty(Cells(a, i).Value) Then
            Exit Sub
        Else
            'Lots of code reading values from the worksheet and printing
            'calculated values to the worksheet 
        End If
    Next b 
Next a 
End Sub

Thanks for your help

You have Cells(a, i) but nowhere is i defined. Should be Cells(a, b)? — chris neilsen
– chris neilsen, Commented Sep 18, 2014 at 19:16

DyRuss · Accepted Answer · 2014-09-18 19:18:31Z

2

Your For loops should be written as:

For a = 10 To XXX

Rather than:

For a = 10 To a = XXX

answered Sep 18, 2014 at 19:18

DyRuss

5024 silver badges12 bronze badges

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

Comments

Trevor Eyre · Accepted Answer · 2014-09-18 19:28:52Z

2

Try this:

Dim a As Integer
'a = 10 'Unnecessary to assign value here, as you assign the starting value in the For loop
Dim b As Integer
'b = 10 'Again, this line not necessary

For a = 10 To Range("B" & Rows.Count).End(xlUp).Row Step 1

   For b = 10 To Worksheets("DistanceLookupTable").Cells(2, Sheet1.Columns.Count).End(xlToLeft).Column Step 1

      If IsEmpty(Cells(a, i).Value) Then '<- do you mean 'b' instead of 'i'? I don't see 'i' assigned anywhere...
         Exit Sub
      Else
        'Lots of code reading values from the worksheet and printing
        'calculated values to the worksheet 
      End If
   Next b 
Next a

And on an unrelated note, you might consider fully qualifying your range in the first for loop (Worksheets("worksheetName").Range("B" & Rows.Count)... instead of just Range("B" & Rows.Count)...) As it is now, it will use the range of the currently active sheet. So unless that is your intention, it's better to be explicit.

edited Sep 18, 2014 at 19:28

answered Sep 18, 2014 at 19:21

Trevor Eyre

7371 gold badge10 silver badges17 bronze badges

2 Comments

Kate Over a year ago

The For loops now work, thank you. However when I put For a = 10 To Worksheets("DistanceLookupTable").(Range("B" & Rows.Count).End(xlUp).Row) Step 1 It highlights it in red and says there's a syntax error

Trevor Eyre Over a year ago

There shouldn't be parenthesis around the range part. You might consider assigning that to a variable instead of hard-coding it directly in the for loop. Like lastColumn = Worksheets("DistanceLookupTable").Range("B" & Rows.Count)... etc. That way you can check what that statement is evaluating to by stepping through the code. And then your for loop would be For a = 10 to lastColumn.

Collectives™ on Stack Overflow

VBA in Excel 2007 won't execute nested for loops

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related