Use VBA to automate goal seek (involving a cell in another worksheet)

**chayanja** · 08-29-2019, 07:11 AM

Hello,

I am very new to VBA and I need some help.

My situation is that, suppose I am working on sheet2, I have an equation in cell B1 which is dependent on the values in cell A1,A2,A3. I want to solve for the value in cell A3 which will make the equation (B1) become zero. I already found out that I could do it by using goal seek; however, I would also like to have the cell A3 changing automatically, to satisfy the equation, whenever I adjust the values in cell A1 and A2. Moreover, the values in my A1 and A2 have to be input in another worksheet. ie. A1=sheet1!F1 and A2=sheet1!F2.

I found this code

Please Login or Register  to view this content.

Using the code above, when I change the values in cell F1,F2 in sheet1, the value in cell B1 in sheet2 changes but the value in cell A3 sheet2 does not automatically change. I have to double click cell B1 and press Enter for goal seek to work and show the result in cell A3 (and then the value in cell B1 become 0), which is what I need.

Does anyone know how can I fix the code above so that when I adjust the cells F1 and F2 in sheet1 then cell A3 in sheet2 automatically change and make B1 become 0?
Unfortunately I cannot use Macros since the model I'm working on will be used in a macro-disabled computer.

Thank you very much for your help

and sorry for my bad English

**MrShorty** · 08-29-2019, 08:59 AM

I am a little confused -- can you, or can you not, use macros? A VBA event procedure is a macro, so you cannot use an event procedure if you cannot use macros.

If you decide that you can use an event procedure for this, I would probably just change your change event to a calculate event procedure. It seems that the vast majority of event procedure examples are for the change event, but there are several other events that are part of the worksheet object, and the calculate event seems more suitable for the task you want here.

If you decide that you cannot use an event procedure, I have a tutorial here showing how to build a simple root finding algorithm in the spreadsheet using circular references (so you will need to make sure iteration is enabled on the computer where you will be using this). https://www.excelforum.com/tips-and-...ind-roots.html
Another example, if it helps: https://www.excelforum.com/excel-for...g-problem.html