If statement problem - #div/0!

[jgold20]

Cubic feet	Filing or Scheduling & CC Fees	Minimum Move	Company Code	Enter Minimum Move Amount	Minimum [FONT=&quot]Price [/FONT]per[FONT=&quot]CF [/FONT]
	YES	YES	L	500	#DIV/0!
			N		#DIV/0!
			NO		#DIV/0!
					#DIV/0!

<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>
</tbody>

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]