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]

yes

 

[Joe4]

Right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor Window:

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 And Target = "Yes" Then
        For Each cell In Range("J3:J6")
            cell.Offset(6, -5).Value = cell.Value
        Next cell
    End If
        
End Sub

When cell G3 is updated to "Yes", this code will automatically copy the values from J3:J6 to E9:E12.

 

[jgold20]

Ty. It might take me a bit to figure out how to add a second VBA on my Mac running Excel 2016. I will let you know when I have the results

 

[Joe4]

It might take me a bit to figure out how to add a second VBA on my Mac running Excel 2016.

I don't use a Mac, but did you try what I recommended?

Right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor Window:

Otherwise, you can just go into the VB Editor, open the VB Project Explorer, double-click on the sheet name you want this to apply to, and paste the VBA code in that window.
(It NEEDS to be in the proper Sheet Module to run automatically).

 

[jgold20]

Receiving the below error in the red bold line:

run time error 13
type mismatch

Sub Clearselected()
Range("e3").ClearContents
Range("e9", "e12").ClearContents
Range("H9", "H12").ClearContents
Range("i3", "i6").ClearContents
Range("K9", "K12").ClearContents
End Sub
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 And Target = "Yes" Then
For Each cell In Range("J3:J6")
cell.Offset(6, -5).Value = cell.Value
Next cell
End If

End Sub

 

[Joe4]

Is it in the same Module as your other code? They usually aren't located in the same modules.
What is the name of the module that the code is in>

The code I gave you is Event Procedure code that MUST go in the appropriate Worksheet module.
The other code is typically found in a Standard module.

 

[jgold20]

It is in the same module. The name is clearselected. They are both supposed to be in the same worksheet. Looking at the top left of clearselected, it is in general. It is 2:30am where I am, I will take a look tomorrow. Thank you for your help, I will let you know the result tomorrow.

 

[Joe4]

It is in the same module. The name is clearselected.

Your Clearselected procedure can go in the clearselected module, but the code I gave you cannot.
That code will only work if placed in the proper sheet module (and it must have that exact name - that is how Automated Event Procedures work - they must follow these strict guidelines).

See here for more details on Event Procedure code: http://what-when-how.com/excel-vba/automatic-procedures-and-events-in-excel-vba/

Note: It doesn't matter that your other code is not in the same module. Standard/general modules like that one can run against any sheet in the workbook.

 

[jgold20]

I had a little time this morning:
1) Inside of visual basic I click on insert module
2) The vba code window opens up
a) The top left dropdown window only has 1 option - GENERAL, according to docs I should be able to select WORKBOOK
b) The top right dropdown window only has 1 option - DECLARATION according I should be able to select OPEN for the event I want to create
c) When I copy the code in, the right side dropdown name changes to Worksheet_change but the left side still will not change

Please note that a module (module1) is showing up under modules

 

[Joe4]

Inside of visual basic I click on insert module

No! You cannot put Event Procedure code in new modules like that - it won't work!!!

It discusses that in the link I provided to you.
Watch this video. In the first minute, it demonstrates EXACTLY where you need to put this type of code.
It absolutely, positively MUST go in one of the existing sheet modules, NOT in a new module that you insert.