+ Reply to Thread
Results 1 to 4 of 4

Loop macro to try various values, and then record the various answers in a new column

  1. #1
    Registered User
    Join Date
    03-31-2008
    Posts
    2

    Loop macro to try various values, and then record the various answers in a new column

    Hello! I am a beginner at VBA. I need some help to do the following in Excel VBA. Any help is greatly appreciated!

    I have created a complex excel sheet that gives me an answer based on some simple inputs. For simplicity let's say there is a cell (A1) which is our input value, and a cell (B1) that contains our answer based on the input in cell A1. What I would like to do is adjust the values of A1 and have the various answers that are produced in B1 copied to say column D with the corresponding input values shown in column C. I would then like the original value put back in cell A1.

    Please note that due to the complexity of the calculations it is not possible to do simple fill-down of the formulas... so I must use VBA. I know conceptually that this is pretty easy to do - looping. I just lack the programming skills to make it. Here are the steps that the VBA program should do...

    1. Get and remember a value from a cell (A1). Say the value is 100.
    2. Do a simple calculation like taking 10% of the value (=10). Perhaps have it look for this setting in cell A5.
    3. Replace the original value for cell A1 from step 1 (=100) with the value from step 2 (=10).
    4. Look at the answer produced in cell B1.
    5. Copy this new value for cell A1 and the answer in cell B1 to columns C and D.
    6. Repeat step 2 to 5 using 10% increments of the original A1 in step 2 - 20,30,40% etc. and stop when the original value is met. The values in columns C and D should be in separate rows with the first pair of input and answer in the first row, and the second pair in the second row, etc.
    (This is just a loop of course)
    7. Write the original value of A1 (=100) into the cell so the sheet is like it was before the macro was ran.

    The macro will be run in a workbook that has multiple sheets. One condition is that the macro points to cells that are within the sheet that the macro is being run from. In other words, the macro should not use cells in other sheets within the workbook. However, I will be using the macro in different sheets that are set-up the same expect for different input values (cell A1). Perhaps the easiest is to have it read the sheet name from where the macro is being run from.

    As I am a beginner, and would like to modify the program a little, and to help my learning, if you have time please add comment fields to explain what the various parts of the program do.

    Thank you in advance!
    timbit
    Last edited by VBA Noob; 03-31-2008 at 05:25 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    How about

    Please Login or Register  to view this content.
    Another way to do this would be to use a table.

    Using your setup, put the values 10, 20, 30...100 into the range D3:D12. In E2 enter the formula =B1. Select the range D2:E12, then data, table, Column Input Cell: a1, OK.

    This will put the output values into the range E3:E12

    rylo
    Last edited by rylo; 04-01-2008 at 12:03 AM.

  3. #3
    Registered User
    Join Date
    03-31-2008
    Posts
    2

    Thank you rylo!

    I never knew about data tables what-if-analysis until now. Thank you for introducing it to me. A very cool tool that I have now begun to incorporate into my workbook.

    It is a hog on processing time so I have disabled the automatic calculation of tables in the Tools > Option > Calculation. I am worried that I will forget to press F9 before using the data output though. Do you have any suggestions to deal with this? ... Perhaps have a macro run by a button that simply forces the calculation of the sheet and tables (is there a VBA command for this?) and writes a message that says "Data tables last run at time X" where X is the time?

    All the best.

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Do you mean you want to force a calculation before you use the numbers in a macro driven process? If so then you could use
    Please Login or Register  to view this content.
    to force a calculation before using the output.

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1