Automating Solver function

[wanttolearn]

Is it possible to automate the use of Solver. To clarify:

Suppose C1 = A1 - B1
C1 -> target cell, which I want to set equal to zero by changing A1.

B1 changes in response to user input. It is a function of say D1 (user input) and A1.

The question I have is the following: if the user changes D1, is it possible to use solver or something else in Excel, such that A1 is recalculated automatically (without user going through the steps of using solver) to make C1=0? Basically, what I am saying is, can we enter a formula or command in A1, which recalculates it so that C1 becomes zero.

Thanks in advance for your help.

[dok112]

Just to clarify, you want to make it so when you change the value of A1 it automatically resets the value of C1 to 0? Is this correct?

[wanttolearn]

Just to clarify, you want to make it so when you change the value of A1 it automatically resets the value of C1 to 0? Is this correct?

I want it such that when I change the value of B1 it automatically recalculates A1 such that C1 (which is a function of A1 and B1) becomes zero.

I wonder whether I have to write my own function for this, or one is already available in Excel.

[wanttolearn]

Can anyone help me with how such a function can be written.

Thanks.

[MrShorty]

It shouldn't be too difficult, though I've never really done that kind of programming.

Start by running Solver manually while recording a macro. That should get you the basic code to work with. Then put that code into a worksheet_calculate event procedure in the sheet where you want it to run. You'll need to add disable events and enable events code at the start and end of the procedure (Solver triggers the calculate event, which can cause a cascade effect).