Attempting to change cells based on values, but nothing changes

J

jUStPunkin

Board Regular
Joined
Mar 23, 2009
Messages
67
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.

VBA Code: 
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Brenda:
Is the code you provided a subset of a VBA Sub ? I do not see your code wrapped within
Public Sub TwoTier()

End Sub

Please provide the rest of your code or a short version of the applicable code.

By the way ... there is an error in the code you provided...

I think Vapor should be in quotes ("Vapor"). Otherwise you have an undefined variable.
Do you have the Option Explicit statement in your code. This would have caught that error.

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

    'ElseIf FeedType = Vapor Then
    ElseIf FeedType = "Vapor" Then  '<< when corrected should look like this
        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If
'....

Your code set L6:L8 to "See Tab" with bugs fixed.

It would help to restructure your code a bit. You are making unnecessary tests in your ElseIf's

What happens with your code if TwoTier is not "Y" or "N", or ReDist is not "Y" or "N", and what values other than "Vapor" or not "Vapor" can FeedType be set to?
You might do some error checking to make your variables are properly set. E.g. If TwoTier is not "Y" or "N" - error message, the same for ReDist, Not sure about FeedType.
 
Upvote 0
There is nothing wrong with your code although it could be improved ( made a bit faster) by using variants instead of ranges. So I suspect you are not calliing it correctly.
I would assume that you want this to run automatially when you change the value in J16 on the Inputs sheet. To do this you need to put the following code inthe worksheet code for the Input worksheet. ( right click on the tab and select view code .
In the worksheet code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False  ' this is necessary to avoid a continuous loop because your code would trigger another change ad infinitum
Call test
Application.EnableEvents = True  ' this is to set the events on again

End Sub

Then the code in a standard module which I have called Test. This includes the changes to use variants instead of ranges. ( actually variants is slightly less typing too!!)
VBA Code: 
Sub test()
Dim FeedType As Variant
    'Dim SlotOrHole As Variant
    Dim Redist As Variant
    Dim RDesignHead As Variant
    Dim DesignHead As Variant
    Dim RTDHead As Variant
    Dim TDHead As Variant
    Dim RTUHead As Variant
    Dim TUHead As Variant
    Dim TwoTier As Variant


     FeedType = Sheets("Input").Range("H12")
    ' SlotOrHole = Sheets("Input").Range("H19")
     Redist = Sheets("Input").Range("H18")
     RDesignHead = Sheets("Spouts2").Range("P3")
     RTDHead = Sheets("Spouts2").Range("R3")
     RTUHead = Sheets("Spouts2").Range("Q3")
     DesignHead = Sheets("Spouts").Range("P3")
     TDHead = Sheets("Spouts").Range("R3")
     TUHead = Sheets("Spouts").Range("Q3")
     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

End Sub
 
Upvote 0
It wouldn't hurt to be very specific since your looking for the Value of a Range. i.e.-
VBA Code: 
 If TwoTier.Value = "Y" Then
 
Upvote 0
Brenda:
Is the code you provided a subset of a VBA Sub ? I do not see your code wrapped within
Public Sub TwoTier()

End Sub

Please provide the rest of your code or a short version of the applicable code.
Click to expand...
There is a lot of code; I can supply the whole thing. I am not very experienced with this, so I am just kind of flying by the seat of my pants. I can usually make things work, but it's probably not the best way to do it.
By the way ... there is an error in the code you provided...

I think Vapor should be in quotes ("Vapor"). Otherwise you have an undefined variable.
Do you have the Option Explicit statement in your code. This would have caught that error.
Click to expand...
I do not; I will check into that.
VBA Code: 
'...
 'If Redistribution is set to no; head heights are pulled from the spouts tab

    'ElseIf FeedType = Vapor Then
    ElseIf FeedType = "Vapor" Then  '<< when corrected should look like this
        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If
'....
Click to expand...

Your code set L6:L8 to "See Tab" with bugs fixed.
Click to expand...

I have fixed this. Thanks.

It would help to restructure your code a bit. You are making unnecessary tests in your ElseIf's

What happens with your code if TwoTier is not "Y" or "N", or ReDist is not "Y" or "N", and what values other than "Vapor" or not "Vapor" can FeedType be set to?
You might do some error checking to make your variables are properly set. E.g. If TwoTier is not "Y" or "N" - error message, the same for ReDist, Not sure about FeedType.
Click to expand...
Wait till you see the rest of it ;) I'm not sure how to restructure it. I know it's long and convoluted, but I'm really not sure how else to do it.

There have to be values in those fields (TwoTier, ReDist, FeedType). And the spreadsheet starts with those values pre-filled; they can be changed. The FeedType is filled in based on input information supplied and cannot be blank (it's a protected cell). The other two, TwoTier and ReDistribution can be left blank. It appears when that happens, the spreadsheet doesn't error, it just doesn't change anymore.

Below is the entire code; everything is working (thanks to some help from in here earlier) except for the top sub, the one shared prior. If you have advice on how to clean this, make it run better, I would greatly accept and appreciate that!

VBA Code: 
Private bRunning As Boolean

Private Sub Worksheet_Activate()
Worksheets("Input").Protect
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

  If bRunning Then Exit Sub


    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

   Dim IsNo As Boolean
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo

 'Shows Spouts if no redistribution, and spouts2 if there is redistribution


    If Range("G_TwoTiered").Value = ("N") Then

        Sheets("5 Downcomer~4 Trough").Visible = False
        Sheets("6 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~6 Trough").Visible = False
        Sheets("10 Downcomer~4 Trough").Visible = False
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = False
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = True
        

 'Hides all two-tiered distributor sheets if Two-Tiered is set to No


    ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("5") Then

        Sheets("5 Downcomer~4 Trough").Visible = True
        Sheets("6 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~6 Trough").Visible = False
        Sheets("10 Downcomer~4 Trough").Visible = False
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = False
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = False
        

    ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("6") Then

        Sheets("5 Downcomer~4 Trough").Visible = False
        Sheets("6 Downcomer~4 Trough").Visible = True
        Sheets("8 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~6 Trough").Visible = False
        Sheets("10 Downcomer~4 Trough").Visible = False
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = False
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = False

    ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("8") And Range("C_TroughsRequired").Value = (6) Then

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

        Sheets("5 Downcomer~4 Trough").Visible = False
        Sheets("6 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~4 Trough").Visible = True
        Sheets("8 Downcomer~6 Trough").Visible = Fals
        Sheets("10 Downcomer~4 Trough").Visible = False
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = False
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = False

    ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("10") And Range("C_TroughsRequired").Value = (6) Then

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

        Sheets("5 Downcomer~4 Trough").Visible = False
        Sheets("6 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~6 Trough").Visible = False
        Sheets("10 Downcomer~4 Trough").Visible = True
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = False
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = False

    ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("12") And Range("C_TroughsRequired").Value = (6) Then

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

        Sheets("5 Downcomer~4 Trough").Visible = False
        Sheets("6 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~4 Trough").Visible = False
        Sheets("8 Downcomer~6 Trough").Visible = False
        Sheets("10 Downcomer~4 Trough").Visible = False
        Sheets("10 Downcomer~6 Trough").Visible = False
        Sheets("12 Downcomer~4 Trough").Visible = True
        Sheets("12 Downcomer~6 Trough").Visible = False
        Sheets("Weighted Density").Visible = False
        'Sheets("Spouts").Visible = False
        

        

        'All ElseIf statements control which sheets are hidden and which are shown based on how many downcomers
        'are selected.
        'If there is no sheet available for the number of downcomers shown, nothing will show

 End If

End Sub
 
Upvote 0
There is nothing wrong with your code although it could be improved ( made a bit faster) by using variants instead of ranges. So I suspect you are not calliing it correctly.
I would assume that you want this to run automatially when you change the value in J16 on the Inputs sheet. To do this you need to put the following code inthe worksheet code for the Input worksheet. ( right click on the tab and select view code .
In the worksheet code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False  ' this is necessary to avoid a continuous loop because your code would trigger another change ad infinitum
Call test
Application.EnableEvents = True  ' this is to set the events on again

End Sub
Click to expand...
So this is where I expected my error works. What confuses me, however, is that the rest of the code (I put the entire code into another response) works fine. It's just this particular section that doesn't do anything. I had the first line in my code, but not the rest. I'll investigate this as well.

Then the code in a standard module which I have called Test. This includes the changes to use variants instead of ranges. ( actually variants is slightly less typing too!!)
VBA Code: 
Sub test()
Dim FeedType As Variant
    'Dim SlotOrHole As Variant
    Dim Redist As Variant
    Dim RDesignHead As Variant
    Dim DesignHead As Variant
    Dim RTDHead As Variant
    Dim TDHead As Variant
    Dim RTUHead As Variant
    Dim TUHead As Variant
    Dim TwoTier As Variant


     FeedType = Sheets("Input").Range("H12")
    ' SlotOrHole = Sheets("Input").Range("H19")
     Redist = Sheets("Input").Range("H18")
     RDesignHead = Sheets("Spouts2").Range("P3")
     RTDHead = Sheets("Spouts2").Range("R3")
     RTUHead = Sheets("Spouts2").Range("Q3")
     DesignHead = Sheets("Spouts").Range("P3")
     TDHead = Sheets("Spouts").Range("R3")
     TUHead = Sheets("Spouts").Range("Q3")
     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

End Sub
Click to expand...
I'll give this a go as well. Curious as to the difference between variants and ranges.
 
Upvote 0
you can get rid of all the if statements controlling the visibility by replacing them with logic statements like this:
VBA Code: 
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
 
Upvote 0
offthelip said:
you can get rid of all the if statements controlling the visibility by replacing them with logic statements like this:
VBA Code: 
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
Click to expand...
Thanks, I will look into this.

But, that part is working; this is the part that isn't working.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

  If bRunning Then Exit Sub


    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.Value = "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
 
Upvote 0
OK, so I have more information. I was actually referring to an incorrect cell earlier, which is why it wouldn't work. However, I fixed that, and now I get another error. I can get the first cell filled in Sheets("Input").Range("L6"). But then I get an error from there, and the spreadsheet crashes. It's a run-time error - "Method 'range' of object'_Worksheet' failed, and when I hit debug, it highlights this line: Sheets("Input").Range("L6") = DesignHead.

It then crashes the spreadsheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Setting worksheet as variable
Replies
4
Views
278
Fluff
Fluff
J
  • Question
Additional VBA Help
Replies
1
Views
240
ranman256
ranman256
J
  • Solved
Hiding sheets based on value
Replies
4
Views
460
jUStPunkin
J
N
  • Question
Lock column ranges based on value in header row, loop through all
Replies
1
Views
480
JustJohn91
J
L
  • Solved
Change macro according to cell value
Replies
7
Views
385
Lux Aeterna
L

Forum statistics

Threads
1,215,523
Messages
6,125,319
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top