My boss has a fairly complicated wb with a limited number of inputs - one of which is a gas price. The workbook (several sheets) calculates results for a 12 month period of usage data to determine cost savings/additional costs for a large industrial customer based on generating (with natural gas) a portion of their own kWh and comparing the projected total electric and gas costs based on levels of generation to what their costs would be if they were not generating. Electric costs are calculated under 2 different scenarios (on different sheets) based on the customer's current utility contract as well as a proposed new contract. The electric costs change based on various criteria that are automated in the wb - but he wants to change the price of gas (to cover a wide range of potential gas prices) and record the net results from each price change to a new sheet along with the gas price used for that scenario. The resulting table might have column headers something like this:
Gas price
Electric $ current K-no generation
Electric $ current K-with generation
Electric $ New contract-no generation
Electric $ New contract-with generation
Projected Gas costs
Current K cost/svgs from generation
New K cost/svgs from generation
The first column (gas price) would essentially be the table from which a "for next" loop would draw a value to input in the first worksheet (where the gas value would then be passed to all the other sheets where needed)
The remaining columns would pull a total value for the 12 months of calculations from each of the sheets based on the "current iteration" gas price selected . Once all the iterations of the loop were completed, he would have a table of values to be able to help the client make a decision on when to generate and when to avoid generation based on the savings or projected additional costs.
I am VERY new to VBA, but I think this could be done - but I haven't figured out how to get all the results from multiple sheets to report back on a single row in a table of results based on the gas price in that row, then loop and return the results from all the worksheets onto the second row of the table for the second gas price, etc.
Obviously we could input a gas price and manually record all the results in a table, then input a second value, record all the results, input a third value and record all the results, until he decided he had enough results - but I think that there must be a better way with VBA. Since I am looking for things that would work better with VBA in order to give me something useful to work on as a learning exercise, I thought this might be the answer. Our work doesn't have a great number of things that I encounter where it would be better/easier with VBA - so my learning curve is pretty slow on this-but I am trying!
Thanks for any help