Attempting to change cells based on values, but nothing changes

[jUStPunkin]

I apologize for the funky title; I wasn't sure how to word this. I have a section of code that is not working. Nothing happens. Currently, I have "Two Tier" as Y. I would expect the code to enter text in the cells specified. But nothing happens - nothing changes.

If I change that cell to N, nothing happens.

If I change it back to Y, nothing happens.

Dim FeedType As Range
    'Dim SlotOrHole As Range
    Dim Redist As Range
    Dim RDesignHead As Range
    Dim DesignHead As Range
    Dim RTDHead As Range
    Dim TDHead As Range
    Dim RTUHead As Range
    Dim TUHead As Range
    Dim TwoTier As Range


    Set FeedType = Sheets("Input").Range("H12")
    'Set SlotOrHole = Sheets("Input").Range("H19")
    Set Redist = Sheets("Input").Range("H18")
    Set RDesignHead = Sheets("Spouts2").Range("P3")
    Set RTDHead = Sheets("Spouts2").Range("R3")
    Set RTUHead = Sheets("Spouts2").Range("Q3")
    Set DesignHead = Sheets("Spouts").Range("P3")
    Set TDHead = Sheets("Spouts").Range("R3")
    Set TUHead = Sheets("Spouts").Range("Q3")
    Set TwoTier = Sheets("Input").Range("J16")


    If TwoTier = "Y" Then

        Sheets("Input").Range("L6") = "See Tab"
        Sheets("Input").Range("L7") = "See Tab"
        Sheets("Input").Range("L8") = "See Tab"

 'If Two Tier is set to yes; all head heights should report the value "See Tab"

    ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "Y" Then

        Sheets("Input").Range("L6") = RDesignHead
        Sheets("Input").Range("L7") = RTDHead
        Sheets("Input").Range("L8") = RTUHead

 'If Redistrubtion is set to yes; head heights are pulled from the spouts2 tab

    ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "N" Then

        Sheets("Input").Range("L6") = DesignHead
        Sheets("Input").Range("L7") = TDHead
        Sheets("Input").Range("L8") = TUHead

 'If Redistribution is set to no; head heights are pulled from the spouts tab

    ElseIf FeedType = Vapor Then

        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If

 

[offthelip]

Which worksheet is this code running in?? If it is the Input worksheet you will cause excel to go into a loop which will cause EXcel to crash. This is because when the worksheet change event is first triggered it write into Input sheet L6 which then triggers the worksheet change event again, and again etc until excel crashes.
To get around this you must add one line before the if statement:

Application.EnableEvents = False   ' add this line
If TwoTier.Value = "Y" Then

This will stop the workhseet change event being triggered, so you must turn events on again at the end of your subroutine or before any other exit, other wise excel won't work properly
So after this bit:

 ElseIf FeedType = "Vapor" Then

        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If
Application.EnableEvents = True  ' add this line

The other thing to try is the code modifcation that I posted in post 3, i.e use variants instead of ranges, it will make it faster too

 

[jUStPunkin]

Which worksheet is this code running in?? If it is the Input worksheet you will cause excel to go into a loop which will cause EXcel to crash. This is because when the worksheet change event is first triggered it write into Input sheet L6 which then triggers the worksheet change event again, and again etc until excel crashes.
To get around this you must add one line before the if statement:

Application.EnableEvents = False   ' add this line
If TwoTier.Value = "Y" Then

This will stop the workhseet change event being triggered, so you must turn events on again at the end of your subroutine or before any other exit, other wise excel won't work properly
So after this bit:

 ElseIf FeedType = "Vapor" Then

        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If
Application.EnableEvents = True  ' add this line

The other thing to try is the code modifcation that I posted in post 3, i.e use variants instead of ranges, it will make it faster too

Amazing! Thank you so much!!

And, I am going to make those variants, instead of ranges - I just wanted to read a little more of that so I understand, before I make the change.

 

[jUStPunkin]

you can get rid of all the if statements controlling the visibility by replacing them with logic statements like this:

Sheets("5 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("5")
Sheets("6 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("6")
Sheets("8 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("8") And Range("C_TroughsRequired").Value = (4)
Sheets("8 Downcomer~6 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("8") And Range("C_TroughsRequired").Value = (6)
Sheets("10 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("10") And Range("C_TroughsRequired").Value = (4)
Sheets("10 Downcomer~6 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("10") And Range("C_TroughsRequired").Value = (6)
Sheets("12 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("12") And Range("C_TroughsRequired").Value = (4)
Sheets("12 Downcomer~6 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("12") And Range("C_TroughsRequired").Value = (6)
Sheets("Weighted Density").Visible = False

@offthelip I'm trying to understand this. So, I have a series of If, ElseIf, Then statements. I can just get rid of all of them, and put this in their place? Do I still need an If statement somewhere? If the value of "G_TwoTiered" = N, I need to hide all of those Sheets - will this do that?

 

[offthelip]

You don't need any if statements, the code should do eactly the same as all your if statements but a lot shorter, and to my mind much easier to follow, you can see exactly what determines the visibilty of each sheet on one line:
it will hide all the sheets If the value of "G_TwoTiered" = N, this is because in each statement e.g.:

Sheets("5 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("5")

the bit of the equation
Range("G_TwoTiered").Value = ("Y")
will return a value of false if the value is N ( i.e. not Y), and so it doesn't matter what value the bit of the equation is:
Range("G_NoofDowncomers").Value = ("5")
the outcome of the two boolean statements will always be false:
( FALSE and TRUE = FALSE)
(FALSE and FALSE = FALSE)
I did note that your equations always hide Weighted Density

 

[jUStPunkin]

You don't need any if statements, the code should do eactly the same as all your if statements but a lot shorter, and to my mind much easier to follow, you can see exactly what determines the visibilty of each sheet on one line:
it will hide all the sheets If the value of "G_TwoTiered" = N, this is because in each statement e.g.:

Sheets("5 Downcomer~4 Trough").Visible = Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("5")

the bit of the equation
Range("G_TwoTiered").Value = ("Y")
will return a value of false if the value is N ( i.e. not Y), and so it doesn't matter what value the bit of the equation is:
Range("G_NoofDowncomers").Value = ("5")
the outcome of the two boolean statements will always be false:
( FALSE and TRUE = FALSE)
(FALSE and FALSE = FALSE)
I did note that your equations always hide Weighted Density

Awesome. Thank you so much for explaining. I will get this worked into the code.

And, truly, thanks for all the help. It's currently working and I'm doing some testing on it with different cases to make sure it's going to return the same results we used to get when we had separate spreadsheets for all of these things.