Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 72 of 72

Thread: IF statment nested within VBA Code

  1. 09-21-2023, 07:47 AM #61
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Ok I've tried that, it seems to work fine so long as C5 = 'Factory Load', the moment C5 is change to 'CFS Loading', that's when it falls down.

    Basically I only need the change C5 to 'CFS Loading' when selecting either Gdynia or Hamburg to New Your or Los Angeles, the rest don't need it, I didn't know how to just have them selectable so was trying to force C5 to 'Factory Load' for the rest, which is why they work but then fails when either Gdynia or Hamburg is selected.

    If it helps below is what Im looking at, think on them as conditional drop downs

    All the other lanes can be set same as the first one ie C5 is Factory Load

    C3 =
Ocean_EU_to_US

    

    C4 =
Genoa to New York

    

    C5 =
Factory Load

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
Genoa/La Spezia to Los Angeles

    

    C5 =
Factory Load

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
Gdynia to New York

    

    C5 =
Factory Load or CFS Loading

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
Gdynia to Los Angeles

    

    C5 =
Factory Load or CFS Loading

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
Hamburg to New York

    

    C5 =
Factory Load or CFS Loading

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
Hamburg to Los Angeles

    

    C5 =
Factory Load or CFS Loading

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
FXT/SOU to New York

    

    C5 =
Factory Load

    

    


    

    C3 =
Ocean_EU_to_US

    

    C4 =
FXT/SOU to Los Angeles

    

    C5 =
Factory Load
    Reply With Quote Reply With Quote
  2. 09-21-2023, 09:14 AM #62
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    what if c3 = "Air?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  3. 09-22-2023, 01:00 AM #63
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    If Air, I have forced it to read 'Factory Load"

    If rC3.Value = "Air" Then rC5.Value = "Factory Load"
    Basically I only need the change C5 to 'CFS Loading' when selecting either Gdynia or Hamburg to New Your or Los Angeles, the rest don't need it.

    I don't know how to just have them selectable so was trying to force C5 to 'Factory Load' for the rest, which is why they work but then fails when either Gdynia or Hamburg is selected.


    If rC3.Value = "Air" Then rC5.Value = "Factory Load"    
    Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value
 
Case "Air" & "#" & "Warsaw to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A16").EntireRow.Hidden = True
    Range("A17").EntireRow.Hidden = False
    Range("A18:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    Range("A32:A76").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Warsaw to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A16").EntireRow.Hidden = True
    Range("A17").EntireRow.Hidden = False
    Range("A18:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
            
Case "Air" & "#" & "Malpensa to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A12").EntireRow.Hidden = False
    Range("A13:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Malpensa to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Heathrow to New York" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7").EntireRow.Hidden = False
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    
Case "Air" & "#" & "Heathrow to Los Angeles" & "#" & "Factory Load"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("A5:A6").EntireRow.Hidden = True
    Range("A7").EntireRow.Hidden = False
    Range("A8").EntireRow.Hidden = True
    Range("A9:A11").EntireRow.Hidden = False
    Range("A12:A21").EntireRow.Hidden = True
    Range("A23:A25").EntireRow.Hidden = True
    ActiveSheet.CheckBoxes("Check Box 111").Value = xlOff
    Reply With Quote Reply With Quote
  4. 09-22-2023, 07:30 AM #64
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Your logic map is a little confusing for me, but I think what you're asking


    If rC3.Value = "Air" Then
    rC5.Value = "Factory Load"    

else if rC4.Value = "Gdynia to New York" then
    rC5.Value = "CFS Loading"
else if rC4.Value = "Gdynia to Los Angeles" then
    rC5.Value = "CFS Loading"    


else if rC4.Value = "Hamburg to New York" then
    rC5.Value = "CFS Loading"    
else if rC4.Value = "Hamburg to Los Angeles" then
    rC5.Value = "CFS Loading"    End if



    Select Case rC3.Value & "#" & rC4.Value & "#" & rC5.Value
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  5. 09-22-2023, 09:27 AM #65
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    HI Paul,

    Sorry if my message was little unclear, I was trying to do the opposite lol

    OK, let try try to explain another way, hopefully this makes more sense to you.


    If the users selects 'Ocean_EU_to_US' in rC3 and any of the below in rC4, then rC5 is visible for them to select either 'Factory Load' or 'CFS Loading'

    Genoa to New York
    Genoa/La Spezia to Los Angeles
    Gdynia to New York
    Gdynia to Los Angeles
    Hamburg to New York
    Hamburg to Los Angeles


    If the user selects any of the below in rC3, then rC5 is forced to 'Factory Load' and the row is hidden, as its not a requirement for the below lanes.

    Air
    Ocean_Asia_to_EU
    Overland

    The only reason I am trying to force 'Factory Load' to the above is because I didn't know how else to adjust your code to work , if there is a better way I'm all ears

    Regards

    Richard
    Reply With Quote Reply With Quote
  6. 09-22-2023, 06:40 PM #66
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    OK, I'm thinking that a more brute force approach might be better and easier to follow

    Try the attached and the macro is longer but IMHO easier to follow

    Be sure to look at the '<<<<<<<<<<<<<<<< comments since I modified some lines temporarily
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  7. 09-25-2023, 01:41 AM #67
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    This looks great but I'm struggling to see where the change is for rC5 cell?

    If the users selects 'Ocean_EU_to_US' in rC3 and any of the below in rC4, then rC5 is visible for them to select either 'Factory Load' or 'CFS Loading'

    Genoa to New York
    Genoa/La Spezia to Los Angeles
    Gdynia to New York
    Gdynia to Los Angeles
    Hamburg to New York
    Hamburg to Los Angeles

    Below example of one of the lanes where different rows are hidden/unhidden depending on what the user selects in rC4 (see previous post #65) (I didn't show all, the rest are in the attachment on post #65)

     
    Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "Factory Load"    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A18").EntireRow.Hidden = True
    Range("A19").EntireRow.Hidden = False
    Range("A20:A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False
    Range("A33:A76").EntireRow.Hidden = True
        
Case "Ocean_EU_to_US" & "#" & "Gdynia to New York" & "#" & "CFS Loading"
    Range("C8:C10").Value = ""
    Range("C11:C17").Value = ""
    Range("C19:C21").Value = ""
    Range("D23:D25").Value = ""
    Range("A5").EntireRow.Hidden = False
    Range("A6").EntireRow.Hidden = True
    Range("A7:A8").EntireRow.Hidden = False
    Range("A9:A19").EntireRow.Hidden = True
    Range("A20").EntireRow.Hidden = False
    Range("A21").EntireRow.Hidden = True
    Range("A22:A25").EntireRow.Hidden = False

    Range("A33:A76").EntireRow.Hidden = True

    The below code is perfect for Air, Overland & Ocean_Asia_to_EU

    Case "Air"      '   -------------------------------------------------------------------------------------------- Mode = Air            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True


    However for Ocean_EU_to_US we will have a mixture of 

    rLoading.Value = "Factory Load"
rLoading.Value = "CFS Loading"
    for the lanes :

    Genoa to New York
    Genoa/La Spezia to Los Angeles
    Gdynia to New York
    Gdynia to Los Angeles
    Hamburg to New York
    Hamburg to Los Angeles


    Do have to change the code like the below:

    rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True

Case "Ocean_EU_to_US"     '   ------------------------------------------------------------ Mode = Ocean EU to US
rLoading.Value = "Factory Load"
rLoading.EntireRow.Hidden = True            
Select Case rLane.Value
                                             
                Case "Gdynia To New York"
                    Range("C8@C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A18").EntireRow.Hidden = True
                    Range("A19").EntireRow.Hidden = False
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    ActiveSheet.CheckBoxes("FBC").Value = xlOff

rLoading.Value = "CFS Loading"
rLoading.EntireRow.Hidden = True            
Select Case rLane.Value
Case "Gdynia To New York"
                    Range("C8@C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A19").EntireRow.Hidden = True
                    Range("A20").EntireRow.Hidden = False
                    Range("A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    ActiveSheet.CheckBoxes("FBC").Value = xlOff
    Oo do I bunch all '"CFS Loading"' lanes (rLane) under ones the heading "CFS Loading" and then repeat for all "Factory Loads" lanes?


    I will review again in case it's me and my lack of understanding, highly possible lol

    Once again, your help is highly appreciated sir.
    Last edited by Scuba; 09-25-2023 at 06:02 AM.
    Reply With Quote Reply With Quote
  8. 09-25-2023, 07:08 AM #68
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    I tried adding in the CFS Loading lanes but kept getting an error 'Select Case without End Select', which has thrown me as I have end Select at the end of each.

    I'm mindful I'm reply to post 67, which you won't have answered/seen yet but I could edit my message to add this bit to it for some reason.

    Private Sub Worksheet_Change(ByVal Target As Range)    Dim r As Range, rMode As Range, rLane As Range, rLoading As Range
    
    Set r = Target.Cells(1, 1)  'only check first cell
    Set rMode = Range("C3")
    Set rLane = Range("C4")
    Set rLoading = Range("C5")
            
    If r.Address <> rMode.Address And r.Address <> rLane.Address Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub


    'Step 1 - configure
    Application.EnableEvents = False
    Unprotect Password:="dlm"


    If Target.Address = rMode.Address Then rLane.Value = "Please Select Origin..."
        
    Shapes("RFR20").Visible = False
    Shapes("RFR40").Visible = False
    Shapes("RFR40HQ").Visible = False
    Shapes("Priority20").Visible = False
    Shapes("Priority40").Visible = False
    Shapes("Priority40HQ").Visible = False
         
    Rows("5:25").Hidden = True
    Rows("79:81").Hidden = True
    Rows("87:" & Rows.Count).Hidden = True
        
    'step 2 - based on Mode, select Lane and configure
    Select Case rMode.Value
        Case "Air"      '   -------------------------------------------------------------------------------------------- Mode = Air
            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True
                
            Select Case rLane.Value
                Case "Warsaw to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A16").EntireRow.Hidden = True
                    Range("A17").EntireRow.Hidden = False
                    Range("A18:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    Range("A32:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                           
                Case "Warsaw to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A16").EntireRow.Hidden = True
                    Range("A17").EntireRow.Hidden = False
                    Range("A18:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                       
                                
                Case "Malpensa to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A12").EntireRow.Hidden = False
                    Range("A13:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                   
                Case "Malpensa to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                   
                Case "Heathrow to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7").EntireRow.Hidden = False
                    Range("A8").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                       
                  
                Case "Heathrow to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7").EntireRow.Hidden = False
                    Range("A8").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A21").EntireRow.Hidden = True
                    Range("A23:A25").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    'ActiveSheet.CheckBoxes("FBC").Value = xlOff
            End Select
        
        Case "Ocean_EU_to_US"     '   ------------------------------------------------------------ Mode = Ocean EU to US
           
            Select Case rLane.Value
                Case "Genoa to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A10").EntireRow.Hidden = True
                    Range("A11").EntireRow.Hidden = False
                    Range("A20").EntireRow.Hidden = False
                    Range("A12:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A26:A32").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                Case "Genoa/La Spezia to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A10").EntireRow.Hidden = True
                    Range("A11").EntireRow.Hidden = False
                    Range("A12:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A26:A32").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "Gdynia to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A11").EntireRow.Hidden = False
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A19").EntireRow.Hidden = False
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                  
                 Case "Gdynia to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "Hamburg to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "Hamburg to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    
                Case "FXT/SOU to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "FXT/SOU to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                 End Select
    Case "Ocean_EU_to_US"     '   ------------------------------------------------------------ Mode = Ocean EU to US
            rLoading.Value = "CFS Loading"   '--------------------------CFS Loading lanes only
            rLoading.EntireRow.Hidden = True
            Select Case rLane.Value
                Case "Gdynia to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5").EntireRow.Hidden = False
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A11").EntireRow.Hidden = False
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A19").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                  
                 Case "Gdynia to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A19").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "Hamburg to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A19").EntireRow.Hidden = True
                    Range("A20").EntireRow.Hidden = False
                    Range("A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "Hamburg to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A11").EntireRow.Hidden = False
                    Range("A12:A19").EntireRow.Hidden = True
                    Range("A20").EntireRow.Hidden = False
                    Range("A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    
                Case "FXT/SOU to New York"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                    
                Case "FXT/SOU to Los Angeles"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A9:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                 End Select
            
            Case "Ocean_Asia_to_EU"     '   ---------------------------------------------------------------- Mode = Ocean Asia to EU
            rLoading.Value = "Factory Load" '-------------------------------------Factory Load Lanes only
            rLoading.EntireRow.Hidden = True
    
            Select Case rLane.Value
                Case "Shanghai to Genoa"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                           
                Case "Xiamen to Genoa"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                Case "Shanghai to Gdynia/Gdansk"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                Case "Xiamen to Gdynia/Gdansk"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                Case "Shanghai to SOU/FXT"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                                        
                Case "Xiamen to SOU/FXT"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A7:A8").EntireRow.Hidden = False
                    Range("A9:A21").EntireRow.Hidden = True
                    Range("A22:A25").EntireRow.Hidden = False
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                  End Select
    
        Case "Overland"     '   ----------------------------------------------------------------------------------- Mode = Overland
            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True
                        
            Select Case rLane.Value
                Case "PL to UK"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7").EntireRow.Hidden = False
                    Range("A8:A21").EntireRow.Hidden = True
                    Range("A22:A23").EntireRow.Hidden = False
                    Range("A24:A25").EntireRow.Hidden = True
                    Range("A26").EntireRow.Hidden = False
                    Range("A27:A32").EntireRow.Hidden = False
                    Range("A38:A74").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                       
                Case "UK to PL"
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A5:A6").EntireRow.Hidden = True
                    Range("A7:A13").EntireRow.Hidden = True
                    Range("A14").EntireRow.Hidden = False
                    Range("A15:A21").EntireRow.Hidden = True
                    Range("A22:A23").EntireRow.Hidden = False
                    Range("A24:A25").EntireRow.Hidden = True
                    Range("A26").EntireRow.Hidden = False
                    Range("A27:A32").EntireRow.Hidden = False
                    Range("A38:A74").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                End Select
            
        'step 3 - based on Lane show check boxes
        Select Case rLane.Value
            Case "Genoa to New York"
                Shapes("RFR20").Visible = True
                Shapes("RFR40").Visible = True
                Shapes("RFR40HQ").Visible = True
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
    
            Case "Genoa/La Spezia to Los Angeles"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Gdynia to New York"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Gdynia to Los Angeles"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Hamburg to New York"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Hamburg to Los Angeles"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "FXT/SOU to New York"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "FXT/SOU to Los Angeles"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Shanghai to Genoa"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Xiamen to Genoa"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Shanghai to Gdynia/Gdansk"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Xiamen to Gdynia/Gdansk"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
            
            Case "Shanghai to SOU/FXT"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
                
            Case "Xiamen to SOU/FXT"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = True
                Shapes("Priority40").Visible = True
                Shapes("Priority40HQ").Visible = True
            
            Case "UK to PL"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = False
                Shapes("Priority40").Visible = False
                Shapes("Priority40HQ").Visible = False
            
            Case "PL to UK"
                Shapes("RFR20").Visible = False
                Shapes("RFR40").Visible = False
                Shapes("RFR40HQ").Visible = False
                Shapes("Priority20").Visible = False
                Shapes("Priority40").Visible = False
                Shapes("Priority40HQ").Visible = False
        End Select
            '    ActiveSheet.Protect Password:="dlm"    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Application.EnableEvents = True
        End Sub
    Reply With Quote Reply With Quote
  9. 09-25-2023, 08:43 AM #69
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    1. The XLSM in #66 does not have SELECT error for me


    2. 
        Rows("1:86").Hidden = False
    Rows("87:" & Rows.Count).Hidden = True
    unhides all rows in 1 to 86 including row 5

    3.
            Case "Air"      '   -------------------------------------------------------------------------------------------- Mode = Air
            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True

and

        Case "Ocean_Asia_to_EU"     '   -------------------------------------------------------------------------------------------- Mode = Ocean Asia to US
            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True

and 

        Case "Overland"     '   -------------------------------------------------------------------------------------------- Mode = Overland
            rLoading.Value = "Factory Load"
            rLoading.EntireRow.Hidden = True

    sets C5 = "Factory Load" but hides the row (I think that was your logic)


    4.
    However for Ocean_EU_to_US we will have a mixture of

    rLoading.Value = "Factory Load"
    rLoading.Value = "CFS Loading"
    for the lanes :

    Genoa to New York
    Genoa/La Spezia to Los Angeles
    Gdynia to New York
    Gdynia to Los Angeles
    Hamburg to New York
    Hamburg to Los Angeles
    Row 5 is left visible from the code in para 2 above and your data validation list has "Factory Load" and CFS Loading" as choices
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  10. 09-25-2023, 09:01 AM #70
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Well that's weird then, I've attached the workbook again so you can see what I'm getting/seeing.

    Everything works OK with the exception of the rLoading, the incorrect rows are not being hidden/unhidden when rloading matches 'CFS Loading', nothing happens when changing rLoading between 'CFS Loading' & 'Factory Load'

    If I select 'Ocean_EU_to_US for rMode and 'Gdynia to New York' for rLane, then when rLoading = 'CFS Loading then Row C19 should be hidden and row 20 should be visible

    Same for Gdynia to Los Angeles
    Hamburg to New York
    Hamburg to Los Angeles

    This is because there are different collection points required depending on CFS or Factory selection in rLoading
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  11. 09-25-2023, 10:49 AM #71
    Paul_Hossler
    Paul_Hossler is online now
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    Try this

    I went back to my prefered way to show all rows and then hide the ones I don't want based on Mode, Lane and Loading since otherwise the show/hide/show, ... gets complicated and I can't keep the logic straight

    That way therre's no need for lots of "Range("A22:A25").EntireRow.Hidden = False" through out since all rows show to start with and only the N/A ones based on Mode, Lane, and Loading need to be hidden

    I think the logic is complicated enough so I'm trying to keep it simple

    I marked adds / changes with <<<<<<<<<<<<<<<<<


    If I select 'Ocean_EU_to_US for rMode and 'Gdynia to New York' for rLane, then when rLoading = 'CFS Loading then Row C19 should be hidden and row 20 should be visible
    There's no logic for Factory Load in the macro. Is there supposed to be?
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  12. 09-26-2023, 01:50 AM #72
    Scuba
    Scuba is offline
    VBAX Regular
    Joined
    Sep 2023
    Posts
    72
    Location
    Hi Paul,

    OK so when I select 'Gdynia to New York' then nothing changes when is select either 'CFS Loading' or 'Factory Loading' in rLoading

    Maybe I'm not explaining myself clearly here, which if so, I apologise for and thank you for your patience with me.

    I going to try and spell it out for you here.

    rloading is defaulted to 'Factory Loading' for all lanes to begin with and the cell is hidden for all rLanes except those shown below

    rLoading row/cell is only visible for the below mention rMode lanes as its only needs to be changed for those lanes,


    if rLane = 'Gdynia to New York' & 'rLoading' = 'Factory Loading' then row 19 is visible & row 20 is hidden.
    if rLane = 'Gdynia to New York' & 'rLoading' = 'CFS Loading' then row 19 is hidden & row 20 is visible

    This logic is needed for the below lanes as well:


    rLane = Gdynia to New York
    rLane = Gdynia to Los Angeles
    rLane = Hamburg to New York
    rLane = Hamburg to Los Angeles

    The user can select between 'CFS Loading' or 'Factory Loading' when the above lanes are selected, everything else can remain as is.

    I don't need to change anything else as it all works correct, I just need to get the correct line 19 & 20 to hide/unhide based on rLoading selection for the above lanes only.

    Hopefully the above is much clearer direction/vision of what I am trying to attain here

    UPDATE!

    I fixed the code to the below and all appears to be working correctly now

    I can't thank you enough Paul for your help, I've learnt so much in a short space of time, nothing like learning on the job lol

    Once again, big thank you, your a star *

           Case "Gdynia to New York"                    If rLoading.Value = "CFS Loading" Then          '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        Range("C19").EntireRow.Hidden = True
                        Range("C20").EntireRow.Hidden = False
                        Else
                        Range("C19").EntireRow.Hidden = False
                        Range("C20").EntireRow.Hidden = True
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    End If
                                                                           
                 Case "Gdynia to Los Angeles"
                    If rLoading.Value = "CFS Loading" Then          '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        Range("C19").EntireRow.Hidden = True
                        Range("C20").EntireRow.Hidden = False
                        Else
                        Range("C19").EntireRow.Hidden = False
                        Range("C20").EntireRow.Hidden = True
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A20:A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    End If
                    
                Case "Hamburg to New York"
                    If rLoading.Value = "CFS Loading" Then          '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        Range("C19").EntireRow.Hidden = True
                        Range("C20").EntireRow.Hidden = False
                        Else
                        Range("C19").EntireRow.Hidden = False
                        Range("C20").EntireRow.Hidden = True
                        Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    End If
                    
                Case "Hamburg to Los Angeles"
                    If rLoading.Value = "CFS Loading" Then          '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        Range("C19").EntireRow.Hidden = True
                        Range("C20").EntireRow.Hidden = False
                        Else
                        Range("C19").EntireRow.Hidden = False
                        Range("C20").EntireRow.Hidden = True
                    Range("C8:C10").Value = vbNullString
                    Range("C11:C17").Value = vbNullString
                    Range("C19:C21").Value = vbNullString
                    Range("D23:D25").Value = vbNullString
                    Range("A6").EntireRow.Hidden = True
                    Range("A12:A18").EntireRow.Hidden = True
                    Range("A21").EntireRow.Hidden = True
                    Range("A33:A76").EntireRow.Hidden = True
                    If Range("M29") = False Then Rows("33:76").EntireRow.Hidden = True
                    End If
    Regards

    Richard.
    Last edited by Scuba; 09-26-2023 at 03:33 AM.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules