Originally Posted by
D_Marcel
Welcome to the forum albe.btz!
Unfortunately I can't open your file right now, but instead of using Do While and Loop, you can set the entire range and then use FOR and NEXT.
[VBA]LAST_ROW = ActiveSheet.UsedRange.Rows.Count[/VBA]
And then:
[VBA]FOR I = 1 TO LAST_ROW
'Your code here
NEXT I[/VBA]
This way, you'll perform the desirable instructions to the last row of your worksheet.
Hope it helps!
Regards,
Douglas
Hi Douglas,
let me see if i got it right, this is my current code:
Sub breakSeasonality()
Dim DayCount
Dim My_Range As Range
Dim Ceiling_Range As Range
Dim CellaCerc
Sheets("GANNT").Activate
Set My_Range = Sheets("GANNT").Range("A1")
Sheets("EXPANDED PERIODS").Activate
Set Ceiling_Range = Sheets("EXPANDED PERIODS").Range("A1")
Do While My_Range.Offset(1, 0) <> ""
Set My_Range = My_Range.Offset(1, 0)
For DayCount = 1 To My_Range.Offset(0, 12)
Set Ceiling_Range = Ceiling_Range.Offset(1, 0)
'Ceiling_Range.Select
Ceiling_Range = My_Range.Offset(0, 9) + DayCount - 1
Ceiling_Range.Offset(0, 1) = "TBD" ' QF SEASONAL
For Each CellaCerc In Sheets("Key Criteria").Range("A2", "A23")
If (((My_Range.Offset(0, 9) + DayCount - 1) >= CellaCerc.Offset(0, 1)) And ((My_Range.Offset(0, 9) + DayCount - 1) <= CellaCerc.Offset(0, 2))) Then
Ceiling_Range.Offset(0, 1) = CellaCerc
Exit For
End If
Next
Ceiling_Range.Offset(0, 2) = My_Range.Offset(0, 1) ' ORI
Ceiling_Range.Offset(0, 3) = My_Range.Offset(0, 2) ' DEST
Ceiling_Range.Offset(0, 4) = My_Range.Offset(0, 0) ' CXR
Ceiling_Range.Offset(0, 5) = My_Range.Offset(0, 3) ' Fare Basis
Ceiling_Range.Offset(0, 6) = My_Range.Offset(0, 4) ' RBD
Ceiling_Range.Offset(0, 7) = My_Range.Offset(0, 5) ' CLASS
Ceiling_Range.Offset(0, 8) = My_Range.Offset(0, 6) ' LEVEL
Ceiling_Range.Offset(0, 9) = My_Range.Offset(0, 7) ' TFC
Ceiling_Range.Offset(0, 10) = My_Range.Offset(0, 8) ' AIF
Next
Loop
End Sub
Where would you insert your fixes?
cheers and thank you