MrExcel Publishing
Your One Stop for Excel Tips & Solutions

command button


Posted by brad on December 21, 2001 10:28 AM

I need help with a macro. How do you code a command button (that's in a cell on the worksheet) so it can adjust a certain cell to make a formula equal to zero?
Any help?!


Posted by Damon Ostrander on December 21, 2001 1:02 PM

Hi Brad,

This can be done using the Excel Goal Seek function. Just add this line of code to you commandbutton Click event code:

[B5].GoalSeek Goal:=0, ChangingCell:=[A5]

where B5 is the cell containing the formula, and A5 is the cell that you want adjusted until the value of B5 is zero.

Happy computing.

Damon

Posted by brad on December 21, 2001 1:57 PM

Damon,
Thanks. what I need with that same code is instead of only adjusting one cell by making one cell with the formula zero, I need the whole column to do it. Make sense? for example instead of only c3 with the formula but c3:c22 and then adjust not only e3 but e3:e22
Thanks

Hi Brad,