Same Cell has input and Output - MAcro

July 8, 2011 at 07:34:52
Specs: Windows 7
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 FN

In 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


See More: Same Cell has input and Output - MAcro

Report •


#1
July 8, 2011 at 08:30:03
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.


Report •

#2
July 11, 2011 at 06:48:06
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.


Report •

#3
July 11, 2011 at 08:43:55
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.


Report •

Related Solutions

#4
July 11, 2011 at 09:55:19
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


Report •

#5
July 11, 2011 at 10:03:28
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.


Report •

#6
July 11, 2011 at 10:14:06
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


Report •

#7
July 11, 2011 at 12:06:01
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.


Report •

#8
July 11, 2011 at 17:52:20
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								7

In 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)


Report •


Ask Question