'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]

Do you have formulae in those cells?

 

[RAJESH1960]

Do you have formulae in those cells?

In the last 3 rows Yes. One is the sum total of each column, one is a formula taken from a different sheet to get the exact match and the third is the difference of the above 2. Also the headings are derived from a formula

 

[Fluff]

But not in the cells you want moving?

 

[RAJESH1960]

But not in the cells you want moving?

No formula in the values that are to be moved

 

[Fluff]

Were there formulae in there that you have copy/pasted as values?

 

[RAJESH1960]

Were there formulae in there that you have copy/pasted as values?

The values are derived from a different code and are posted in this sheet. But without any formula.

 

[Fluff]

If it's always pulling from rows 15/16 & pasting into 1/2, then the blank cells are not empty, which is why the code does not work.
You will need to revisit the code that populates the cells

 

[RAJESH1960]

If it's always pulling from rows 15/16 & pasting into 1/2, then the blank cells are not empty, which is why the code does not work.
You will need to revisit the code that populates the cells

It should pull the values of the last 2 cells in columns O to AD and paste it in the cells in the last empty cells of columns A to N. So that the total sum in the last doesn't change. This is what I am expecting.

 

[Fluff]

I know what you are expecting. But the code will not work if the "blank" cells are not empty.

 

[RAJESH1960]

I know what you are expecting. But the code will not work if the "blank" cells are not empty.

You mean blank but not empty