Hi all,
This is my first post in this forum. I really hope some here could help me.
I'm a Regulatory Analyst working for a water utility. I'm trying to calculate outputs based on various scenarios. The problem I have is that I need to generate outputs for 12,500 scenarios, which I think can be done using a macro/VBA code. I have no prior experience for this kind of problem.
Please see attached excel workbook for your reference. You can ignore the "RevReq_Scenarios" tab. The below steps are related to "Opex&Capex by Scenarios" tab.
I have outlines below on how I would like the macro to be setup:
For each scenario in column X,
1. copy and paste opex data in columns Y to AB into cells AN10:AQ10 [1st set of input variable]
2. copy and paste capex data in columns AC to AF into cells AN11:AQ11 [2nd set of input variable]
3. copy and paste the output values in cells AN12:AQ12 into columns AH to AK [output values]
The above steps must be looped such that it continues for all scenarios 1 to 12,500 in column X. Please note that this process involves data being overwritten for each loop in cells AN10:AQ10 and cells AN11:AQ11.
At the end of the process, columns AH to AK must be filled with output data for each scenario.
For example,
Scenario 1:
Step 1: copy and paste as values, input data (Opex) in cells: Y10 to AB10 into cells: AN10 to AQ10.
Step 2: copy and paste as values, input data (Capex) in cells: AC10 to AB10 into cells: AN10 to AQ10.
Step 3: copy and paste as values, output data (RR) in cells: AN12 to AQ12 into cells: AH10 to AK10.
and this process repeats until scenario 12,500.
PLEASE NOTE that I have reduced the number of scenarios to 100 instead of the original 12,500 as the file was too big to be uploaded.
I will really appreciate and be really grateful for anyone that can provide me the exact macro code that I can use to solve this problem. Jaawid.
This is my first post in this forum. I really hope some here could help me.
I'm a Regulatory Analyst working for a water utility. I'm trying to calculate outputs based on various scenarios. The problem I have is that I need to generate outputs for 12,500 scenarios, which I think can be done using a macro/VBA code. I have no prior experience for this kind of problem.
Please see attached excel workbook for your reference. You can ignore the "RevReq_Scenarios" tab. The below steps are related to "Opex&Capex by Scenarios" tab.
I have outlines below on how I would like the macro to be setup:
For each scenario in column X,
1. copy and paste opex data in columns Y to AB into cells AN10:AQ10 [1st set of input variable]
2. copy and paste capex data in columns AC to AF into cells AN11:AQ11 [2nd set of input variable]
3. copy and paste the output values in cells AN12:AQ12 into columns AH to AK [output values]
The above steps must be looped such that it continues for all scenarios 1 to 12,500 in column X. Please note that this process involves data being overwritten for each loop in cells AN10:AQ10 and cells AN11:AQ11.
At the end of the process, columns AH to AK must be filled with output data for each scenario.
For example,
Scenario 1:
Step 1: copy and paste as values, input data (Opex) in cells: Y10 to AB10 into cells: AN10 to AQ10.
Step 2: copy and paste as values, input data (Capex) in cells: AC10 to AB10 into cells: AN10 to AQ10.
Step 3: copy and paste as values, output data (RR) in cells: AN12 to AQ12 into cells: AH10 to AK10.
and this process repeats until scenario 12,500.
PLEASE NOTE that I have reduced the number of scenarios to 100 instead of the original 12,500 as the file was too big to be uploaded.
I will really appreciate and be really grateful for anyone that can provide me the exact macro code that I can use to solve this problem. Jaawid.