If statement not working

[sulavsingh]

I cant get the > if statement portion to work. Please let me know what I am doing wrong.


Private Sub worksheet_Change(ByVal Target As Range)
'
' What_If Macro
'This Macros deletes one cell if another has a value in it

'For cell inputs (Medium Duty, Severe Service, and Heavy Duty)
Dim Medium As Variant
Dim Severe As Variant
Dim Heavy As Variant

Medium = Cells(14, 11)
Severe = Cells(38, 11)
Heavy = Cells(58, 11)

Application.EnableEvents = False
If IsNumeric(Target.Value) Then
'Clear the Invalid entries
Range("L4").ClearContents
Range("L5").ClearContents
Range("L28").ClearContents
Range("L29").ClearContents
Range("L48").ClearContents
Range("L49").ClearContents
If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
ElseIf Target.Address = "$K$28" Then
Range("K29").ClearContents
ElseIf Target.Address = "$K$29" Then
Range("K28").ClearContents
ElseIf Target.Address = "$K$48" Then
Range("K49").ClearContents
ElseIf Target.Address = "$K$49" Then
Range("K48").ClearContents
ElseIf Target.Value > Cells(14, 11) Then
Range(Target.Address).ClearContents
ElseIf Target.Value > Severe Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
ElseIf Range(Target.Address).Value > Heavy Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
End If

Else
MsgBox ("Invalid Input: ") & Target.Value
Range(Target.Address).ClearContents
Target.Next = "Invalid Input"




End If


Application.EnableEvents = True

End Sub

thanks for your help

[mdmackillop]

Step through it to see where the error is. You would be best to limit the event code to changes in the relevant cells.
Personally, I would use a select case structure.
Can you post your workbook so we can test your code? (Manage Attachments in the Go Advanced Reply section)

[sulavsingh]

thanks for your help in this. I have attached my workbook. I took out part of the code (while I was trying to fix it). Please cut and paste the code above into the What IF tab's code.

thanks

[Bob Phillips]

This work for me



Private Sub worksheet_Change(ByVal Target As Range)
'What_If Macro
'This Macros deletes one cell if another has a value in it

'For cell inputs (Medium Duty, Severe Service, and Heavy Duty)
Dim Medium As Variant
Dim Severe As Variant
Dim Heavy As Variant

Medium = Cells(14, 11)
Severe = Cells(38, 11)
Heavy = Cells(58, 11)

Application.EnableEvents = False
If IsNumeric(Target.Value) Then
'Clear the Invalid entries
Range("L4,L5,L28,L29,L48,L49").ClearContents
If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
ElseIf Target.Address = "$K$28" Then
Range("K29").ClearContents
ElseIf Target.Address = "$K$29" Then
Range("K28").ClearContents
ElseIf Target.Address = "$K$48" Then
Range("K49").ClearContents
ElseIf Target.Address = "$K$49" Then
Range("K48").ClearContents
ElseIf Target.Value > Cells(14, 11) Then
Target.ClearContents
ElseIf Target.Value > Severe Then
Target.Next.Value = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Target.ClearContents
ElseIf Range(Target.Address).Value > Heavy Then
Target.Next.Value = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Target.ClearContents
End If
Else

MsgBox ("Invalid Input: ") & Target.Value
Target.ClearContents
Target.Next.Value = "Invalid Input"
End If

Application.EnableEvents = True

End Sub

[sulavsingh]

Whats not happening for me is that when I input a value in forexample in K4 (What IF) tab and it is greater than K14 I get no message telling me of the error. Are you getting that error?

[mdmackillop]

If I change K4 to 4100, the code clears K5 as it should. Do you expect more? There are too many variables to test them all.

[sulavsingh]

The only other thing that I would like is that when you enter a value into K4 it checks to see if it is greater than the value already in K14 (Total Allison and Eaton). And if it is larger it tells you to enter a lower value

[mdmackillop]

You're trying to do too much in one statement
Try

If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
ElseIf Target.Address = "$K$28" Then
Range("K29").ClearContents
ElseIf Target.Address = "$K$29" Then
Range("K28").ClearContents
ElseIf Target.Address = "$K$48" Then
Range("K49").ClearContents
ElseIf Target.Address = "$K$49" Then
Range("K48").ClearContents
End If

If Target.Value > Cells(14, 11) Then
Range(Target.Address).ClearContents
ElseIf Target.Value > Severe Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
ElseIf Range(Target.Address).Value > Heavy Then
Target.Next = "Enter Lower Amount"
MsgBox ("Enter Lower Amount")
Range(Target.Address).ClearContents
End If

[sulavsingh]

Thank you very much, especially for the prompt replies. That fixed my problem.