Dividing a cell by constant value

[jlloyd123]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("C43:E43")
On Error GoTo ws_exit:
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Target.Value = Target.Value / .50
End If
ws_exit:
Application.EnableEvents = True
End Sub

I've read about decimals and I've tried to do ".50" and "1/ 2" - When I run this code, it just deletes what I have in my cell. PLEASE HELP! I have to do one cell divided by .50 and one divided by .30. I don't really know what I'm doing at all (obviously) but this is code that I used on a cell I needed to multiply by 1.25. I just changed the * to a /.

 

[AlphaFrog]

Your code works fine. It didn't delete the value in the cell.

I'm not sure what result you want. Dividing by 0.5 is the same as multiplying by 2. Is that what you're going for?

This would multiply the entry by 1.25

Target.Value = Target.Value * 1.25

 

[jlloyd123]

I'm trying to divide by .50 for one set and then .30 for another set. I have this code set up already for a cell that I need to multiply by 1.25 and it's working perfectly. But, the division is not working.

 

[AlphaFrog]

I'm trying to divide by .50 for one set and then .30 for another set. I have this code set up already for a cell that I need to multiply by 1.25 and it's working perfectly. But, the division is not working.

What sets?

The code you gave above doesn't multiply by 1.25

Can you describe exactly what cell(s) get multiplied by 1.25 and what cell(s) get divided by .5 ?

 

[jlloyd123]

I need

cells C43 and E43 to divide by .5

cells C44 and E44 to divide by .3

I'm all set on the other one that multiplies.

Thank you!

 

[AlphaFrog]

You can have only one Workshet_Change procedure for a given worksheet. So all the "sets" have to be integrated into one procedure like below.

I made up the one that multiplies.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]If[/color] Target.Count = 1 [color=darkblue]Then[/color]
        [color=darkblue]If[/color] Target.Value <> "" And IsNumeric(Target) [color=darkblue]Then[/color]
            
            Application.EnableEvents = [color=darkblue]False[/color]
            
            [color=green]'cells C43 and E43 to divide by .5[/color]
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Target, Range("C43,E43")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                Target.Value = Target.Value / 0.5
                
            [color=green]'cells C44 and E44 to divide by .3[/color]
            [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] Intersect(Target, Range("C44,E44")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                Target.Value = Target.Value / 0.3
                
            [color=green]'cells C45 and E45 to multiply by 1.25[/color]
            [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] Intersect(Target, Range("C45,E45")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                Target.Value = Target.Value * 1.25
                
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            
            Application.EnableEvents = [color=darkblue]True[/color]
            
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[jlloyd123]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Count = 1 Then
        If Target.Value <> "" And IsNumeric(Target) Then
            
            Application.EnableEvents = False
            
            'cells C42 and E42 to divide by .5
            If Not Intersect(Target, Range("C42,E42")) Is Nothing Then
                Target.Value = Target.Value / 0.5
                
            'cells C43 and E43 to divide by .3
            ElseIf Not Intersect(Target, Range("C43,E43")) Is Nothing Then
                Target.Value = Target.Value / 0.3
                
            'cells C9,E9,C12,E12,C15,E15 to multiply by 1.25
            ElseIf Not Intersect(Target, Range("C9,E9,C12,E12,C15,E15")) Is Nothing Then
                Target.Value = Target.Value * 1.25
                
            End If
            
            Application.EnableEvents = True
            
        End If
    End If

YAY! I know it's so close now! I've updated the code to include the actual cells. When I move over to my workbook, this is not listed as a macro to run now.. I assume the code that names it is missing? I really cannot thank you enough for your help! No one I know is able to help with this.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

 

[jlloyd123]

Any idea on how to get the code to show as a macro to run?

 

[AlphaFrog]

This code is an event macro based on your original code (that you said you already successfully used). It runs automatically when you make a change to one of the cells. It's not the kind of macro the user runs.

To install the code in the correct place:
- Right-click on the Sheet tab that has the cells you want to multiply\divide.
- Select View Code from the pop-up context menu
- Paste the code in the worksheet's code module

Then make a change to one of the cells.