PDA

View Full Version : Control strings



albe.btz
08-23-2017, 05:52 AM
Hi guys,

I'm struggling to fix this and i can't find a way to go ahead.

I have to fix two things in this dataset:

1) including a check that skips "expired" and "null" lines from a loop

20152

In the attached you will see that there is a module that duplicates line from GANNT sheet into EXPANDED PERIODS times the value of duration cells. GANNT sheet is populated with another module from another source (that i had to remove to have 1MB file). When the sub breakSeasonality runs, it stops when the first row contains "expired" or "null" values. I need to add a string that checks if there are such values and if yes skips the line until a good one is found.

I've tried to put something but i miserably failed :(

Hope i can get your help

D_Marcel
08-23-2017, 06:23 AM
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.

LAST_ROW = ActiveSheet.UsedRange.Rows.Count

And then:

FOR I = 1 TO LAST_ROW
'Your code here
NEXT I

This way, you'll perform the desirable instructions to the last row of your worksheet.

Hope it helps!

Regards,

Douglas

albe.btz
08-23-2017, 07:30 AM
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.

LAST_ROW = ActiveSheet.UsedRange.Rows.Count

And then:

FOR I = 1 TO LAST_ROW
'Your code here
NEXT I

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

Bob Phillips
08-23-2017, 08:00 AM
Change



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

to


For Each CellaCerc In Sheets("Key Criteria").Range("A2", "A23")

If My_Range.Offset(0, 9).Value = "expired" Then

'do nothing
ElseIf (My_Range.Offset(0, 9).Value + DayCount - 1) >= CellaCerc.Offset(0, 1) And _
(My_Range.Offset(0, 9).Value + DayCount - 1) <= CellaCerc.Offset(0, 2) Then
Ceiling_Range.Offset(0, 1) = CellaCerc
Exit For
End If
Next

albe.btz
08-23-2017, 08:09 AM
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


I tried to change do while and loop but when it finds a row with the current situations it crashes:



CXR
ORI
DEST
Fare basis
RBD
Class
Level
TFC
AIF
Start Travel Date
End Travel Date
Sale End Date
Duration






CX
LON
AKL
KLARRGB8
K
Y
1279
226
1504.5
expired
expired
31/03/2019
null






that was the purpose of my thread: finding a solution to skip these lines

albe.btz
08-23-2017, 08:32 AM
Change



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

to


For Each CellaCerc In Sheets("Key Criteria").Range("A2", "A23")

If My_Range.Offset(0, 9).Value = "expired" Then

'do nothing
ElseIf (My_Range.Offset(0, 9).Value + DayCount - 1) >= CellaCerc.Offset(0, 1) And _
(My_Range.Offset(0, 9).Value + DayCount - 1) <= CellaCerc.Offset(0, 2) Then
Ceiling_Range.Offset(0, 1) = CellaCerc
Exit For
End If
Next

it seems working, however it gets to a row and stops saying: runtime error 113, type mismatch

i checked the row (after putting a debug.print) and it says row 69 but there is nothing wrong in that one....+

the error sits in this string: For DayCount = 1 To My_Range.Offset(0, 12)

i just tried to check it with a dumm data where after 16 duplications (the specified value in the cell) it stopped....

Bob Phillips
08-23-2017, 09:02 AM
Probably something wrong in this cell My_Range.Offset(0, 12)

albe.btz
08-24-2017, 12:50 AM
It's solved. I just put an IF statement before the loop. easy