Hello guys I'm looking for a macro program to use the data in a row as an input to the next row calculations and as well as output from the previous row.

Example:

Column A B C D E F Row 1 : A1 B1 C1 D1 E1 F1 Row 2 : A2 B2 C2 D2 E2 F2 Row3 : A3 B3 C3 D3 E3 F3 . . . RowN : AN BN CN DN EN FNIn the first row , the user will enter the values manually (it is fixed one). In the next row, I use formulas and some conditions to check the values for Row1 and calculate the output and display it in Row2. Then, I wanna use the row2 output as input to Row3 and need to calculate the output using the above specified formulas. I need to repeat this step until the end of the rows or until to satisfy my conditions.

I'm newbie in macro programming, so looking for any assistance to complete this work.

Thanks

re: " Then, I wanna use the row2 output as input to Row3 and need to calculate the output using the above specified formulas"Where will the output from the Row 3 input be displayed? Row 4, meaning that Row 4 will contain formulas referencing Row 3?

...and then Row 4 will be the input to Row 5 whose output will be displayed in row 6, and so on?]

Did I get that right?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Derby Yes, you are right. Row4 input referring to Row3 (and check condition) and display the result. It will keep on going for 'N' number of rows.

So, why can't you just use the same formulas and conditions that you are using in Row 2 in Rows 4, 6, 8, etc. which each cell referring to the cell above it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, I agree with that.I'm able to use those formulas in excel cells and get the output /input for limited range. But for larger number of rows( say 1000's) I want to use the macro program. So I have the problem in writing the condition in macro in loop like structure. P.S. I want outputs in the Rows 2,3,4,5 etc. consecutively and at the same time Row 2 output will be used as a reference for Row3 and Row3 output will be used as a reference for Row4.

Thanks

Since you haven't given us any examples of the formulas or conditions, there is not a lot we can offer. We need specifics as to what these formulas and conditions are before we can suggest a method to accomplish your goal.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Is there any way to send PM about my formulas , conditions and calculations to you (through here or to email). Pls, let me know. Thanks

All questions, including the data require for us to answer them, should be posted in the forum unless specifically instructed by a member to do otherwise. If questions are answered via PM or email, then the discussion will not get saved in the archives for others to share and learn from.

Is there a reason that you can not post the informaiton here?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi Debby V1 V2 V3 V4 V5 Capacity 30 30 24 30 24 Charging Rate 3 6 3 6 3 Input Data 6 10 12 18 12 Total Units 5 Time V1 V2 V3 V4 V5 Demand Supply 0:00 6 10 12 18 12 21 6 1:00 4.8 8.8 10.8 16.8 10.8 21 7 2:00 3.4 7.4 9.4 15.4 9.4 21 23 3:00 0.4 4.4 6.4 12.4 6.4 12 4:00 19 5:00 1 6:00 12 7:00 18 8:00 7In the given example, V1-V5 represents the units (total- 5no’s) that needs to be calculated by the system based on the demand and supply. The capacity and maximum charging rate for those units are given in the first two lines and those values are constant.

The ‘supply’ column refers to the maximum available quantity to provide to those 5 units.

The “Demand” column is the sum of individual units demand limited to its charging rate. i.e. The system is able to charge the units upto its maximum charging rate.

Condition: Demand = ∑ Min (V1-V5, Charging rate)

Calculations:

For time 00:00, the data for those units are taken from ‘Input Data’ row. The user will give this data manually for the first row only.

For 01:00, the row has to check for the Demand Vs Supply Condition for each cell (from previous row) and to give the output (which is going to be the input to next row).

Condition:

1. Calculate the demand for the first row limited to its charging rate. (Demand - 21)

2. Check whether the demand is lesser or higher than Supply. (Supply – 6; Demand > Supply)

3. If Demand is lesser or equal to Supply, meet the demand. (i.e- Row2 = Row1 - Demand)

4. If Demand is higher than Supply, divide the supply by total number of units and meet the partial demand. (i.e- Row2 = Row1 – (Demand=Supply/No. of Units))

For 02:00, same as above procedure.

For 03:00, same as above procedure.

I’m did those calculations manually in excel spread sheet writing formulas in individual cells. Now I need to write the macro to do this calculation automatically, but I’m not good in programming. So I’m looking for some help to do this calculation using macros.

I had written macros for the conditions for those calculations. But I’m able to execute for only one step not able to use it consecutively (not able to get Row3 output and as well as use as input for next row)

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History