Can VBA record multiple iteration wb results with for next loop from a range of data?

[Elmarie]

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

[Paul_Hossler]

1. Big project

2. An example workbook would help, along with some worked 'examples'

3. Excel's Scenario Manager might be able to do it without VBA

4.The easiest way might be to have a list of the gas prices, and a loop that uses each price on the other sheets to calculate, and then put the results on the Summary sheet

The attachment has a simple example for some made up gas prices and dummied up data



Option Explicit

Sub Demo()
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Summary")
For i = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
Worksheets("E_current_no").Range("A2").Value = .Cells(i, 1).Value
Worksheets("E_current_with").Range("A2").Value = .Cells(i, 1).Value

Application.CalculateFull

.Cells(i, 2).Value = Worksheets("E_current_no").Range("N3").Value
.Cells(i, 3).Value = Worksheets("E_current_with").Range("N3").Value

Next i
End With

Application.ScreenUpdating = True

MsgBox "Done"

End Sub




There are other (and more elegant) ways to do it

[Elmarie]

I removed identifying info from his wb and it is attached. You will see on the lower right of the first sheet that he has cells reporting cost of power and a calculation of the "benefit of contract" (as opposed to the straight Large Power rates of the utility for other customers - which is calculated on the 2nd tab). The cost of gas input is on the 3rd tab and the 4th tab has a table of gas price history. His goal, as I understand it, is to recalculate the first 3 tabs for each values on the gas price table and return the key sums contained on the first three tabs to the corresponding line with the gas price. So if it was running the first loop using $3.32 as the gas price (cell b3), then the various answers from the first 3 tabs would all get deposited on line 3 in columns E-?? (not sure how many things he is wanting returned to this table). Then when the next iteration ran (for $4.06 gas price in cell B4), all the answers from running the calculations would appear on line 4, etc.

First - is it do-able? Next, is it possible for me to ever figure out how to do it (with help)? I like challenges and I have found that the best way to learn is to have a real life project that you want to accomplish in order to learn a new skill- but it requires some help beyond all the training videos, etc - because they aren't really designed for the project you have to accomplish, LOL. I have been wading through a video VBA training program I talked my bosses into paying for, but I still have a long way to go - but at least it no longer terrifies me and it doesn't look like COMPLETE Greek anymore, LOL.

Thanks for any and all help/suggestions! Hopefully the file will come through. Not sure if I am doing this right.
22038

[Paul_Hossler]

On Sheet3, B174 = 3.75 for Jan-17, the Price of Gas (?)

On the first 3 sheets, what cell(s) have the Price of Gas?

I didn't see a reference to Sheet3, nor did I notice anything that looks like a formula that uses a POG quantity


BTW, when I see $ like this, I usually suspect that there's an error in the spreadsheet


22039

[Elmarie]

On Sheet3, B174 = 3.75 for Jan-17, the Price of Gas (?)

On the first 3 sheets, what cell(s) have the Price of Gas?
The first TWO tabs do not include the price of gas as they are solely related to the cost of electricity based on consumption (current customer contract & the utility's general large power tariff). The 3rd tab uses the price of gas in cell N21 to evaluate the cost of generation and the net effect on the company of the changes in the electric costs + cost of generation. When generation occurs, it alters the pricing of all power purchased from the utility due to significant power factor reductions. So even though the customer forgoes paying for X amount of electricity to the utility, the remaining amount that is metered gets priced differently than their normal consumption (which typically is billed with a 90%+ power factor). The customer's generation results in creating heat which is in turn used to create steam for other operations that (without generation) would normally be created by the burning of natural gas. So when the customer generates electricity and uses the heat of the generation to create the steam that they need for other operations, the natural gas burn for generation serves two purposes. The whole concept of the wb is to evaluate at what gas price there is a "break-even" between the gas savings/cost (normal gas purchase costs for steam vs. gas purchased for generation and steam) + savings on electricity (normal electric costs for total power purchase vs. cost of electricity for reduced metered consumption due to generation) Since the electric costs are altered (per unit) based on power factor, which is in turn altered by the reduced amount of kWh metered in relation to the KVars metered (which generally do not drop due to generation), it gets pretty complicated and difficult to evaluate at an overall level. You really have to project each month individually and test different gas prices against the electric utility's contract terms to determine whether it is cost-advantageous to generate a significant portion of their power needs.

I didn't see a reference to Sheet3, nor did I notice anything that looks like a formula that uses a POG quantity.
At this point, he has not created a reference to the table on Sheet 3. He just has a single value for gas on the 3rd tab (Large Power with cogen running) at cell N21 which is then used in the column below (N23:N34). But the desire is to be able to load all those gas prices from the Sheet 3 list and see what the net results of the calculations would be. On that 3rd tab, you can see in cells C23:C34 the "Normal" hourly usage of the plant. Cells G23:G34 provide the projected hourly generation (a variable that he is able to set) and the net amount [hourly demand] goes to C6:C17. He has built in some "oops" months throughout the year (4x) when the projected 24x7 generation would fail briefly and the demand would remain high for the month - because that is the way it works with utilities. You get billed for the peak demand that the meter registers for the month. So if your normal need is 55K on an hourly basis and you manage to produce 35K from generation every hour of the month, the net amount that the meter would register for your bill would be 20K. But if your generation unit goes down for just a portion of an hour and you therefore register a metered demand of 55K at some point during that one hour - that is what your bill will be based on.

BTW, when I see $ like this, I usually suspect that there's an error in the spreadsheet
I am not sure what I am looking at here - so I don't know how to respond. Is this the result of an attempt to get VBA to do the multiple iterations and record the results? Without column headings it is hard to know what I am looking at, so I don't know where to look for an error in his wb (if there is one). Based on the dates showing on your sample, I tried placing those gas values into the wb and did not get your results anywhere. The table is simply actual historical gas prices. The intent is NOT to use the table as a vlookup for any specific date. If that were the idea, we would simply put 12 fixed values in for the monthly gas and have done with it. The idea is to provide gas as a variable and keep trying different values to compare that cost to the change in the electric costs as the result of generation.

Thank you!


22039

[Paul_Hossler]

Can't help with the first issues, but if you go to '2017 Contract payment' worksheet in your attachment in post #3, and unhide the rows between 20 and 153, you can see the numbers in column L even without the headings