If statement problem - #div/0!

[jgold20]

[TABLE="width: 598"]
<colgroup><col width="91" style="width: 68pt;"><col width="99" style="width: 74pt;"><col width="89" style="width: 67pt;"><col width="99" style="width: 74pt;"><col width="123" style="width: 92pt;"><col width="97" style="width: 73pt;"></colgroup><tbody>[TR]
[TD="class: xl68, width: 91, align: center"]Cubic feet[/TD]
[TD="class: xl73, width: 99, align: center"] Filing or Scheduling & CC Fees[/TD]
[TD="class: xl71, width: 89, align: center"]Minimum Move[/TD]
[TD="class: xl73, width: 99, align: center"]Company Code[/TD]
[TD="class: xl73, width: 123, align: center"]Enter Minimum Move Amount [/TD]
[TD="class: xl75, width: 97, align: center"]Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot]CF [/FONT][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl72, width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]YES[/TD]
[TD="class: xl76, width: 99, align: center"]L[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"]500[/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"]N[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"]NO[/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66, align: center"][/TD]
[TD="class: xl76, width: 99, align: center"] [/TD]
[TD="class: xl74, width: 123, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[TD="class: xl77, align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]
In the above example, when cubic feet is 0, it returns a #div/0! error. The statement I am using is =IF(G3="YES",(I3/E3),0) for minimum move. If I use the drop down and change minimum move to NO, it returns a 0. I have tried everything I could think of and still receive the #div/0!. Is there any way to avoid the error. I understand why it is happening

 

[jgold20]

Missed your question - Drop-down is a data validation box

 

[jgold20]

Same result as before with the new code:

Change to NO
a) Check1 macro invoked - OK
Changed to YES
a) Check1 macro invoked - OK

SAME MESSAGE FOR BOTH

 

[jgold20]

Removed the drop-down box and the results are the same

 

[jgold20]

I figured out what the problem is, my drop-down box was uppercase. Once I changed the YES in the VBA code to YES it worked. Now I want to clear the contents of the cell and if it is NO. This is not working. Below is the code:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim cell As Range



' Check to see if cell G3 updated
If Target.Address = Range("G3").Address Then
If Target.Value = "YES" Then
Application.EnableEvents = False
Range("E9:E12").Value = Range("J3:J6").Value
Application.EnableEvents = True
If Target.Value = "NO" Then
Application.EnableEvents = False
Range("E9:E12").ClearContents
Application.EnableEvents = True
End If
End If
End If

End Sub

 

[jgold20]

Joe, I appreciate all of your time and effort. I used the below and it is working. In addition, I found a second way to do it with a module calling a worksheet. Ty

Private Sub Worksheet_Change(ByVal Target As Range)


Dim cell As Range



' Check to see if cell G3 updated
If Target.Address = Range("G3").Address Then
If Target.Value = "YES" Then
Application.EnableEvents = False
Range("E9:E12").Value = Range("J3:J6").Value
Application.EnableEvents = True
Else
If Target.Address = Range("G3").Address Then
If Target.Value = "NO" Then
Application.EnableEvents = False
Range("E9:E12").ClearContents
Application.EnableEvents = True
End If
End If
End If
End If

End Sub


Sub min_move_yes(ByVal Target As Range)
If Not Intersect(Target, Range("G3")) Is Nothing Then
Select Case Range("G3")
Case "YES": Module1
End Select
End If
End Sub
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


Dim cell As Range

Range("E9:E12").Value = Range("J3:J6").Value

End Sub

 

[Joe4]

You are welcome.

Glad you got it all sorted out now.

 

[jgold20]

If you have a chance, I posted another question:

[h=2]VBA code - have 1 woking, need to add another one[/h]