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]

I did not see a video anywhere?

http://what-when-how.com/excel-vba/automatic-procedures-and-events-in-excel-vba/

 

[jgold20]

I MANAGED TO GET THE CODE IN - IT IS NOT EXECUTING

 

[jgold20]

 

[jgold20]

 

[jgold20]

Unable to insert Mac screenshot

 

[Joe4]

Sorry, forgot the link in the previous post.
Here it is: https://www.youtube.com/watch?v=iYmITPeIX5k
Pay close attention at about the 45 second mark to about the 1 minute 45 second mark.

 

[jgold20]

Sequence of events
1) Right click
2) View Code
3) Copy and paste supplied code
a) Drop-down changes to worksheet
b) Drop-down changes to change
4) Close VBA editor
5) In worksheet
a)Drop-down in G3 is changed to yes
b) J3 - J6 calculate price correctly
c) E9 - E12 remain blank ( they are not = to J3 - J6)

 

[Joe4]

Is your drop-down a Data Validation Box, or are you use ActiveX or other Form controls?

Let's see if your Macro is being invoked/called properly. Temporarily add a few message boxes to the code, like shown below:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
[COLOR=#ff0000]    MsgBox "Macro is invoked", vbOKOnly, "Check1"[/COLOR]

'   Check to see if cell G3 updated
    If Target.Address = Range("G3").Address And Target = "Yes" Then
[COLOR=#ff0000]        MsgBox "Range G3 update to Yes", vbOKOnly, "Check2"[/COLOR]
        For Each cell In Range("J3:J6")
            cell.Offset(6, -5).Value = cell.Value
        Next cell
    End If
        
End Sub

Any manual change to your sheet should cause the first message to be displayed.
Changing G3 to "Yes" should cause the second message box to also be displayed.

If neither of those is happening, that macro is not being called to run.

 

[jgold20]

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

SAME MESSAGE FOR BOTH

 

[Rick Rothstein]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
[COLOR=#ff0000]    MsgBox "Macro is invoked", vbOKOnly, "Check1"[/COLOR]

'   Check to see if cell G3 updated
    If Target.Address = Range("G3").Address And Target = "Yes" Then
[COLOR=#ff0000]        MsgBox "Range G3 update to Yes", vbOKOnly, "Check2"[/COLOR]
        For Each cell In Range("J3:J6")
            cell.Offset(6, -5).Value = cell.Value
        Next cell
    End If
        
End Sub

I realize you were just adding to the code the OP posted, but I have a couple of comments regarding the code...

1) As written, the code will error out if a multi-cell paste operation is performed on the sheet. The If..And..Then statement needs to be broken into two separate If..Then tests, first for the cell address, then for the "Yes" value.

2) The For..Next loop can be replaced by a single line of code.

3) I disabled events while the cells are being copied so that the copy process does not trigger the Change event again.

Here is the modified code which implements the above three comments...

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim cell As Range
  
  MsgBox "Macro is invoked", vbOKOnly, "Check1"

'   Check to see if cell G3 updated
  [B][COLOR="#0000FF"]If Target.Address = Range("G3").Address Then[/COLOR][/B]
    [B][COLOR="#0000FF"]If Target.Value = "Yes" Then[/COLOR][/B]
      MsgBox "Range G3 update to Yes", vbOKOnly, "Check2"
      [B][COLOR="#006400"]Application.EnableEvents = False[/COLOR][/B]
      [B][COLOR="#FF0000"]Range("E9:E12").Value = Range("J3:J6").Value[/COLOR][/B]
      [B][COLOR="#006400"]Application.EnableEvents = True[/COLOR][/B]
    [B][COLOR="#0000FF"]End If[/COLOR][/B]
  [COLOR="#0000FF"][B]End If[/B][/COLOR]
        
End Sub