PDA

View Full Version : Need help with VBA code for entering cell value based on another cell value



Embl
04-08-2019, 07:23 AM
As the title says, I need help with a VBA code for automating the output of one cell based on input on another, and vice versa.
Cell A1: 50 (variable)
Cell A2: 100 (variable)
Cell A3: 150 (A1+A2) (fixed)

So I basically need A1=A3-A2, and A2=A3-A1.
Is that doable?

Kerry H
04-08-2019, 11:06 AM
What you're describing is, obviously, a circular reference problem if used as formulas in an Excel cell. In VBA you can get around the problem fairly easily (in general).
However, I think we need more information on your specific problem to know when/how you want the variables to be manipulated.

Embl
04-08-2019, 12:06 PM
Thanks for answering. Yes you're right, it seems I'm looking for a method for circular references.
I'd like the the values to update as soon as I've entered a value into A1 or A2, but if that's not possible, I guess I could add a botton to activate the macro.
I just need it for something very basic as to calculate the amount of hot and cold water to mix to get a certain amount of a specific temperature liquid.

Bob Phillips
04-08-2019, 04:39 PM
You could try worksheet event code, but what happens when A3 changes?



Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

With Me

Select Case True

Case Not Intersect(Target, .Range("A1")) Is Nothing

.Range("A2").Value = .Range("A3").Value - Range("A1").Value

Case Not Intersect(Target, .Range("A2")) Is Nothing

.Range("A1").Value = .Range("A3").Value - Range("A2").Value
End Select

ws_exit:
Application.EnableEvents = True
End Sub