You are here:

Excel/Auto Recalculate Excel Macro

Advertisement


Question
QUESTION: I need to implement a macro into my spreadsheet to recalculate itself until it outputs the desired value. How it works is pretty simple but I don't know the language to create it. On the worksheet titled "Calculating Stage 2" I have column A which goes 90-0 in 0.1 increments. So column A occupies cells A1-A901. Step one would need to delete all of Column B so it can start from scratch. Step 2 would take the value from A1 and paste it to B1. This will fill C1-C101 with a value and D1-D101 with a different value. Step 3 would look at the value of C1 and D1, and if C1 was equal or greater than D1 then it would end the macro. If C1 was less than D1 after inputting the value into B1 it would move down a row and start back at Step 1. So utt would delete all of column B and copy A2 to cell B2 and so on until it reached row 901. If C901 was still less than D901, then it would change the value of B901 to "X" and end the macro.

ANSWER: I suppose we could spend time on a macro to do that, but the Excel Solver is designed for this kind of thing.

Press F1 to open the HELP SEARCH window, and search for SOLVER, then select "DEFINE AND SOLVE A PROBLEM".  This will show you where to find it and how to turn it on if it wasn't installed initially with your Excel.

---------- FOLLOW-UP ----------

QUESTION: Yes and I suppose I could use the "if" function as well but there is a reason I wanted a macro. I figured the macro would be pretty simple.

Answer
No, based on your original question, an IF formula would also be a lot more work than necessary.  The Solver is designed to do this exact thing, change a cell based on your parameters and instructions until you achieve a result elsewhere on your document.  It's the correct tool.

----------
You are correct in that solving this with the Solver would take about 30 seconds.  A macro would take unnecessarily MUCH longer to construct and test.... I apologize if you misunderstood my guiding you to the correct tool as any sort of unwillingness to discuss other methods.  I do, however, stand by the answer.

Now that I've seen the rating and odd response you posted, instead of just continuing the discussion with me IN the thread, then yes, perhaps unwillingness is now a result.  Odd choices.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.