CODEWITHSUNDEEP
excelloopsvba
George
George

Reputation: 73

VBA Do Loop Doesnt Work

I created the following Do loop:

    Sub Test2()
Set sht = Worksheets("Calculations")
LastRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row + 1
LastCol = (LastRow - 2) * 3
i = 1
r = 3
c = 5

Do Until r > LastRow
    sht.Cells(r, c).Select
    RangeName = "Airline_" & i
    Set Cell = sht.Cells(r, 5)
    sht.Names.Add Name:=RangeName, RefersTo:=Cell
        Do Until c > LastCol
            RangeName = "Airline_" & i
            Set Cell = Application.Union(Range("Airline_" & i), sht.Cells(r, c))
            sht.Names.Add Name:=RangeName, RefersTo:=Cell
        c = c + 3
        Loop
    i = i + 1
    r = r + 1
Loop

End Sub

At first glance, everything appears to be OK. But when r becomes 4, it looks like the Do until c > LastCol doesn't work.

Below are some screenshots, as explanation:

First row, which works: Screenshot 1 (working row)

Second row, which isn't working: Screenshot 2 (not working)

Upvotes: 0

Views: 216

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

I prefer FOR loops.

Also there was a lot of redundancy.

Sub Test2()
Dim sht As Worksheet
Dim Lastrow&, LastCol&
Dim r&, c&
Dim RangeName as String

Set sht = Worksheets("Calculations")
Lastrow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row + 1
LastCol = (Lastrow - 2) * 3 'need to be careful with this, too many rows will go off the page in columns.


For r = 3 To Lastrow
    RangeName = "Airline_" & r - 2
    Set cell = sht.Cells(r, 5)
    For c = 8 To LastCol Step 3
        Set cell = Union(cell, sht.Cells(r, c))
    Next c
    sht.Names.Add Name:=RangeName, RefersTo:=cell
Next r

End Sub

Upvotes: 2

Related Questions