VBA code to populate a table

**roheba** · 03-09-2010, 01:47 PM

We have a complex analysis process which determines a result by altering two variables. The first variable is an integer between 4 and 15, inclusive. The second variable is an integer between 7 and 40, inclusive. We have written a VBA loop which steps through all the variable combinations and ultimately identifies the optimal settings to maximize the result. Here's how:

Please Login or Register  to view this content.

On further study, we have concluded that we need to better understand the implications of the two variables' interaction, not just the point where they produce the biggest "bang." Therefore, we want to build a table of all the results. We need to be able to look at the whole matrix of results defined by a column for each first variable value and a row for each second variable value. This table will show us a "sweet spot" where the intersect of several contiguous rows and columns provide superior, but not always optimal results.

Unfortunately, this VBA coding is beyond our level of skill. Can anyone assist us?

**rylo** · 03-09-2010, 09:59 PM

Hi

What do you have in AD4?

rylo

**roheba** · 03-12-2010, 02:16 PM

AD4 is the result of the calculations based on the variables in G3 and M3. The larger the number in AD4, the better the result. The above code finds the largest value in AD4 and the values of G3 and M3 which generated that largest result.

**rylo** · 03-14-2010, 05:53 PM

Hi

Can you attach an example file showing your structure, and the formula you have in AD4.

rylo

**roheba** · 03-18-2010, 04:33 PM

AD4 displays as a percent with one decimal place. The formula in AD4 isn't important, but it represents the return on an invested amount and can be a negative or positive number. What we're trying to do is build a table with the integer values 4 - 15 as column headings, and 7 - 40 as row descriptors, and the calculated ROI value from Cell AD4 at the intersection of each row and column. So, the first cell in the table would be in the column labeled "4" and the row labeled "7", and would contain the amount from Cell AD4 when Cell G3 is "4" and Cell M3 is "7". The next cell (to the right) in the table would be in the "5" column and the "7" row, and would contain the amount from Cell AD4 when Cell G3 is "5" and Cell M3 is "7". The last cell in the table would be in the "15" column and the "40" row, and would contain the amount from Cell AD4 when Cell G3 is "15" and Cell M3 is "40".

Here's a partial example (only thru Column 10):

**rylo** · 03-23-2010, 01:34 AM

Hi

As your code evaluates the result of AD4, it would seem to me that having a formula in that cell in the example file is integral to the process you are trying to deliver. If you don't have a formula there, then your original code isn't going to do much, and we can't evaluate what is going on if we don't have that formula - or a least a formula that uses your 2 input variables for evaluation purposes.

Looking at your output tho, have you tried to use the Data, Table function?

rylo

**roheba** · 03-25-2010, 07:23 PM

Thanks for the suggestion to explore the possibility of using a two-variable data table. I looked into that with high hopes, but came away disappointed. In my situation, after the input cells (G3, M3) are populated, there are many intermediate calculations that depend on but don't reference the input cells. The formula (in cell AD4) which yields the ultimate result of the process does not reference the input cells. Hence, the two-variable data table would not work.

In conversation with some smarter-than-me colleagues, I have become convinced that the FORMULA in AD4 is actually very important in that it can be calculated in the data table I'm trying to build which is far more efficient than running the process, copying the result, and PasteSpecial-ing it into the table. The actual formula in AD4 is: =(AD5/AA2)-1 It is dividing the final value of an investment by the amount initially invested and subtracting 1 from the resulting quotient to give the percent gain or loss of the investment. The goal is to build a table of these and ponder the results until the range of most effective variable settings is apparent.

**shg** · 03-25-2010, 07:41 PM

In my situation, after the input cells (G3, M3) are populated, there are many intermediate calculations that depend on but don't reference the input cells. The formula (in cell AD4) which yields the ultimate result of the process does not reference the input cells. Hence, the two-variable data table would not work.

Are you sure about that? The formula in AD4 need not be a direct dependent of the input cells, it can (and commonly would) be a distant descendant.

**roheba** · 04-02-2010, 11:26 AM

You're absolutely right! This one is SOLVED. I tried the two-variable data table again... and this time it worked. This is the perfect solution for my need. Thanks for your attention. And, thanks for challenging my response that the data table was not the answer.