If, And, ElseIf, Else Statement Help

[RMXByker]

Hello all, back at it again with an easy question for the experts out there. I need the following to happen;

If F7 does not equal blank, and F10 does equal blank, then L10 should be filled with the text "3) Remove Shunt"
If F7 does not equal blank, and F10 does not equal blank, then L10 should be filled with the text "3) Remove 1st Stage Shunt"
Lastly, If F7 equals blank, then L10 equals blank.

I've attached my current code which is not functional. It is placed in the Worksheet Change structure but I'm still learning this stuff and I'm certainly in a bit deeper than my knowledge currently. The failure it's giving is nondescript in my eyes as it just gives a "out of stack space" or it just crashes Excel. Sorry I can't give more information on this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    
        If "$F$7" <> "" And "$F$10" = "" Then
            Range("$L$10").Value = "3) Remove Shunt"
            
        ElseIf "$F$7" <> "" And "$F$10" <> "" Then
            Range("$L$10").Value = "3) Remove 2nd Stage Shunt"
        
        Else
            Range("$L$10").Value = """"
            
            
        End If
            
End Sub

Thank you...

 

[Joe4]

A few things:

This is invalid syntax:

If "$F$7" <> "" And "$F$10" = "" Then

It should be:

If Range("$F$7") <> "" And Range("$F$10") = "" Then

Secondly, Worksheet_Change event procedure usually run upon manual data entry into specific cells, and the "Target" cell range is usually referenced in your VBA code.
Are you trying to run this on existing data, or as data is being entered?
What should be the triggering mechanism for this to run?

 

[RMXByker]

Thanks for your input. I'll update the syntax. Ideally this would be triggered whenever there is a change to either F7 or F10. I'm just not sure how to set the trigger for either of those 2 cells.

 

[RMXByker]

Code has been updated to below;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)    
        If Range("$F$7") <> "" And Range("$F$10") = "" Then
            Range("$L$10").Value = "3) Remove Shunt"
            
        ElseIf Range("$F$7") <> "" And Range("$F$10") <> "" Then
            Range("$L$10").Value = "3) Remove 2nd Stage Shunt"
        
        Else
            Range("$L$10").Value = ""
            
            
        End If
            
End Sub

Now I'm getting a "Out of Stack Space" issue on the first line of code if I try to add a value to F7 and then I get a "Method 'Value' of object 'Range' failed after a few seconds when I try to delete that value out.

 

[Joe4]

You want to write it so that it only runs when F7 or F10 are manually updated, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at once
    If Target.Count > 1 Then Exit Sub

'   Check to see if cell F7 or F10 updated
    If Target.Address(0, 0) = "F7" Or Target.Address(0, 0) = "F10" Then
        If Range("F7") <> "" Then
            If Range("F10") = "" Then
                Range("L10").Value = "3) Remove Shunt"
            Else
                Range("L10").Value = "3) Remove 2nd Stage Shunt"
            End If
        Else
            Range("L10").Value = ""
        End If
    End If

End Sub

 

[RMXByker]

Thank you Joe. Works flawlessly. I will work on reading an understanding the additions you made so I don't make the mistake again. Thanks again for your time!

 

[Joe4]

You are welcome.

If you don't understand any part of the code or have any questions, feel free to ask away!