'for' Loop

[RAJESH1960]

Hello guys,

I am trying to do something new. With the help of a VBA code, in every case, last 2 Values of last 2 cells of columns D-15 to D-28 to be cut and pasted to Columns D-1 to D-14 in the next empty cell, with the help of a code. There are 2 sheets in the workbook. One is the original and the other is the expected result after the code is run.

I am not even sure how it is possible to cut the values using a VBA code. I just need a “for” loop to get the cell values from columns 15-28 to the corresponding 1-14. Finally, the sum total value of A17:AB17 should not change after the code is run. Something like lRow = Cells(16,lColNo ).End(xlUp).Row.value = value.
This is the raw sheet

Query Random Sort cut paste with the help of code.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	1	2	3	4	5	6	7	8	9	10	11	12	13
3	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	2	3	4	5	6	7	8	9	10	11	12	13	14
4	3	4	5	6		8	9	10	11	12	13	14	15	16	17	3	4	5	6	7	8	9	10	11	12	13	14	15
5	4	5	6	7		9		11	12		14		16		18	4	5	6	7	8	9		11	12	13	14	15	16
6	5	6	7	8		10		12	13		15					5	6	7	8	9	10		12	13		15	16
7		7		9					14								7		9	10				14			17
8		8		10					15								8		10	11				15
9		9		11					16								9		11					16
10		10							17								10							17
11		11							18								11							18
12		12							19								12							19
13
14
15
16
17	15	77	25	60	11	40	24	50	154	33	65	39	58	45	66	15	77	25	60	56	40	24	50	154	46	65	87	58
18	1519
19
Original

Cell Formulas
Range	Formula
A17:AB17	B17	=SUM(B2:B16)
A18	A18	=SUM(A17:AB17)

and this is the expected answer after the code is run

Query Random Sort cut paste with the help of code.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	D-1	D-2	D-3	D-4	D-5	D-6	D-7	D-8	D-9	D-10	D-11	D-12	D-13	D-14	D-15	D-16	D-17	D-18	D-19	D-20	D-21	D-22	D-23	D-24	D-25	D-26	D-27	D-28
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	1	2	3	4	5	6	7	8	9	10	11	12	13
3	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	2	3	4	5	6	7		9	10	11	12	13	14
4	3	4	5	6	8	8	9	10	11	12	13	14	15	16		3	4	5	6	7	8		10	11		13	14
5	4	5	6	7	9	9	9	11	12	18	14	14	16	15			5	6	7					12			15
6	5	6	7	8		10	10	12	13	19	15	15	16	16			6	7	8					13
7	17	7	11	9				8	14		12		17				7		9					14
8	18	8	12	10				9	15		13						8		10					15
9		9		11					16								9		11					16
10		10		10					17								10							17
11		11		11					18
12		12							19
13		4							11
14		5							12
15
16	50	86	48	81	28	40	43	67	177	70	90	68	91	76	31	6	54	25	60	18	21	7	27	117	21	36	54	27
17	1519
18	1519
19	0	Differnce must be 0
After Code

Cell Formulas
Range	Formula
A16:AB16	B16	=SUM(B2:B15)
A17	A17	=SUM(A16:AB16)
A18	A18	=Original!A18
A19	A19	=A17-A18

 

[Fluff]

I mean they are not empty. Blank and empty are two different things.

 

[RAJESH1960]

I mean they are not empty. Blank and empty are two different things.

Is there a way I can attach my file.?

 

[Fluff]

You can upload to a share site such as OneDrive, Google Drive or DropBox, mark for sharing & post the link you are given to the thread.

 

[RAJESH1960]

You can upload to a share site such as OneDrive, Google Drive or DropBox, mark for sharing & post the link you are given to the thread.

Ok. Let me try Google Drive. I had done that a long ago. Will share the link.

 

[RAJESH1960]

Fluff, Please check this link. I hope I have done it right. If not working, let me know. Will try again.

Project Daily .xlsm

drive.google.com

I haven't run the new macro 'Match' in this sheet. This is the macro that does need editing. All the other macros are running perfectly. Press the button 'Clear workings' to run the macro again.

 

[RAJESH1960]

After running all the macros, the final result is perfect. I don’t want the daily cash to be the same on all days. So, I was trying to cut 2 cells from the end and replace them in other columns in the beginning without changing the total sum of daily. That is the 'Match' macro for.

 

[Fluff]

The only problem is that you haven't change the range, your data is going down to about row 620, not row 16

 

[RAJESH1960]

The only problem is that you haven't change the range, your data is going down to about row 620, not row 16

I replaced 16 with 619. What do I write in place of -14 ? Does the whole code need to be edited ?

 

[RAJESH1960]

Got it right Fluff. Thanks once again.

 

[Fluff]

You're welcome & thanks for the feedback.