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

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