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!

 

[VoG]

Try

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

 

[bpgolferguy]

You are once again the master....thank you so much!

 

[bpgolferguy]

Try

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

VoG, I've started adding other ranges to your code (I assume that's how I'm supposed to) but now it's stopped it from working.....any idea why? Here's the code as I have it in the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'Fairway Rd 1
If Not Intersect(Target, Range("G4:G400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("G3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
'Fairway Rd 2
If Not Intersect(Target, Range("H4:H400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("H3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
'Fairway Rd 3
If Not Intersect(Target, Range("I4:I400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("I3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

When I enter data into the cells now, it just returns the number I entered....not the product of the equation.

 

[VoG]

Maybe events are disabled: in the code window press CTRL + G then in the Immediate window type

Application.EnableEvents = True


and press Enter.

 

[bpgolferguy]

That worked! Weird....any reason why it just stopped? Will I have to do that often?

 

[VoG]

Maybe it errored whilst events were disabled. Try adding the line in red

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'Fairway Rd 1
If Not Intersect(Target, Range("G4:G400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("G3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
'Fairway Rd 2
If Not Intersect(Target, Range("H4:H400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("H3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
'Fairway Rd 3
If Not Intersect(Target, Range("I4:I400")) Is Nothing Then
    If Target.Value <> "" Then
        Application.EnableEvents = False
        With Target
            .Value = .Value / Range("I3").Value * 100
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

 

[bpgolferguy]

VoG, I posted that line at the beginning of the code, but I just found it did it again. I thought it wasn't working, but I did the enableevents=true in the immediate window and it worked again.

My question is this....what exactly does that line you had me enter in your last post do? Also, what does this part do:

If Target.Value <> "" Then
Application.EnableEvents = False

I would always like it to calculate the fields I have in the formula anytime (unless there is no data entered in the cell, I just want it to be blank)....if I understand correctly, that says if nothing is entered send the enableevents to false?

 

[VoG]

The Target.Count statement says only run if a single cell is changed.

Application.EnableEvents=False is to prevent an infinite loop. If we didn't have that, K4 would be divided by K3 which would trigger the code again, dividing K4 by K3 again and so on until you got an out of stack space error.

 

[bpgolferguy]

I understand.....so what would be the cause of it setting it to false where I have to tell it true again? Is it because it's still a work in progress? Maybe once it's going like its supposed to it won't ever do that?

 

[VoG]

It would have failed with events set to false if you had deleted several cells, for example. The Target.Count should avoid that.