Enter value in cell and computer formula in same cell

[bpgolferguy]

Hi, what formula or code would I need so that when I enter a value into cell K4, it then computes the following equation:

K4/K3

I would like the result to be in cell K4. Thanks!

 

[bpgolferguy]

Help?

 

[GlennUK]

Don't you think that would be confusing ... typing something into a cell and it suddenly changes into another value.

 

[bpgolferguy]

Not really.....because I'm basically entering data in the form I'm receiving it from my users, but I need to divide that data by the data in another cell. And I can't use an extra cell for each piece of data to just stick a formula in because it would take up way too much room. I just want it to do the calculation once I've entered the data.

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "K4" Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / .Offset(-1).Value
    End With
    Application.EnableEvents = True
End If
End Sub

 

[bpgolferguy]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "K4" Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / .Offset(-1).Value
    End With
    Application.EnableEvents = True
End If
End Sub

Thank you VoG.....I would like to do this for K4:k400, and then possibly couple more ranges.....G4:G400, etc. Is there a way I can add the ranges I wish to do it for? Or do I need to create what you've posted for each range?

 

[VoG]

Try this: note that it will divide the cell by the value in the cell above it, not always K3.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K400")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / .Offset(-1).Value
    End With
    Application.EnableEvents = True
End If
End Sub

 

[bpgolferguy]

Try this: note that it will divide the cell by the value in the cell above it, not always K3.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K400")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / .Offset(-1).Value
    End With
    Application.EnableEvents = True
End If
End Sub

Oooo, now I do need it to divide by K3 all the time.....can i just replace .Offset(-1).Value with K3. value?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K400")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / Range("K3").Value
    End With
    Application.EnableEvents = True
End If
End Sub

 

[bpgolferguy]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K4:K400")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        .Value = .Value / Range("K3").Value
    End With
    Application.EnableEvents = True
End If
End Sub

That worked perfect....thank you! One last thing....if I click on K4 and hit delete, it returns a "0" in the cell. Can I make so that if I don't enter anything in the cell, it returns nothing?