Auto-calculate in the same cell when entering a value

[argoguy]

If I want to divide automatically by 1.16 when I enter a value into a cell how do i do this?

ie.

I want any number entered into a cell divided by 1.16

so if I input 20 it will automatically change into 17.24 in the same cell i entered the 20 into.

Please help me and thanks very much.

A.J.

 

[Jonmo1]

ANY Cell? Or just one in particular?

 

[mikerickson]

This routine (put in the code sheet for the worksheet in question) will do what you want. I'm guessing that, once installed, finding typos will be a pain.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If IsNumeric(Target.Value) Then Target.Value = Target.Value / 1.16
    Application.EnableEvents = True
End Sub

 

[Jonmo1]

you can restrict it to one particular cell like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("a1").Address Then
    If WorksheetFunction.IsNumber(Target.Value) Then
        Application.EnableEvents = False
        Target.Value = Target.Value / 1.16
        Application.EnableEvents = True
    End If
End If
End Sub

P.S. Wouldn't it be easier to just have a formula somwhere else on the sheet to do the math?

=A1/1.16 ?

 

[argoguy]

Yeah that would be a lot easier, however I'm dealing with my manager and he's set on a specific Idea, and doesn't want any other cells on the report. I'm trying to get it to change the numbers from gross sales into net as he enters them in.

thanks I'm going to try the suggestions now

this is great thanks so much for all your help!!!

 

[Jonmo1]

And you can use a whole range of cells (instead of just one cell) like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B13")) Is Nothing Then
    If WorksheetFunction.IsNumber(Target.Value) Then
        Application.EnableEvents = False
        Target.Value = Target.Value / 1.16
        Application.EnableEvents = True
    End If
End If
End Sub

 

[argoguy]

Problems with formatting of the cell in question...

When I format the cell as $20.00 it won't change the value upon entry..

please advise if you can think of why this is...

Thanks guys you are awesome!