Buttons & Calculations

[richardking]

Hi Guys,

I have an excel workbook that uses an input box to ask for a number, then use that number as part of a formula. That formula is then finally put on a cell. There is just one button to run the macro for the input box to ask for a number.

Now, I want to do something beyond that.

Attached is a sample workbook that is modified for simplicity and for me to explain it better.

The revised workbook now has three buttons and two different formulas.

Formula 1: (A2+A3)/Rate1
Formula 2: A2/(Rate2+A3)

What the three buttons do are as follows:

-First button: Tells the user to give a number between 101-200, then use that number as part of formula 1. And then put formula 1 in cell A5. (Another cell in a different sheet, which is in A2 and has nothing to do with the macro, has a formula on it that gives me a rate based on the value on A5. The higher the rate, the better.)

-Second button: This is similar to the first button, but asks for a number between 1-100 instead and uses that number as part of formula 2. Formula 2 is then put on cell A5. (Cell A2 of the above mentioned, which is outside of the macro, does its thing again and gives me a rate. The higher, the better.)

-Third button: The third button somewhat combines the first two buttons. What it should do should be in this sequence: It would ask for a first number input, then a second number input. Run the calculation using the first number input and formula 1. Run another calculation using the second number input and formula 2. Now, whichever formula gives me a higher rate in cell A2 of Sheet2 will be retained in A5.

No need for an error handler if the user does not want to follow the number requirements for each of the buttons.
Thanks guys.

[mjr veverka]

... a higher rate in cell B2 will be retained ...

???
I think you mean 'A2' in sheet2 ?

[jmac1947]

Hi Richard,
attached is a very simplistic solution to what I understand your requirements to be from the OP and correction?? from Porucha.

I am very sure there are better methods to get the answer you need, I just took the simple way to demonstrate a possible solution. The variables storing the individual A5 results was just to allow me to see what was happening while stepping through the code, they could easily be removed.

Hope this gets you further along your journey

Cheers

[richardking]

Thank you for pointing that out porucha vevrku, I had that corrected.
Thank you jmac1947, it did the job. I just had to change the If result_1 <= result_2 Then into If result_1 > result_2 Then. Everything else is great.
I truly appreciate it guys.

[mjr veverka]

I see I have for you, after meat, mustard ... : ) ... but try:
... of course without error handling ... : )

Please Login or Register  to view this content.