'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

 

[offthelip]

try this code:

Sub test()
inarr = Range("a1:AB14")
 For j = 1 To 28
  For i = 1 To 14
   If inarr(i, j) = "" Then
    Cells(i, j) = inarr(i - 2, j)
    Cells(i + 1, j) = inarr(i - 1, j)
     Exit For
   End If
  Next i
 Next j
 
   
End Sub

 

[RAJESH1960]

try this code:

Sub test()
inarr = Range("a1:AB14")
 For j = 1 To 28
  For i = 1 To 14
   If inarr(i, j) = "" Then
    Cells(i, j) = inarr(i - 2, j)
    Cells(i + 1, j) = inarr(i - 1, j)
     Exit For
   End If
  Next i
 Next j
 
  
End Sub

I am getting compile error. Are the variables ok.?
Sub offthelip()

Dim inarr As Long, j As Long, i As Long

inarr = Range("1:AB14")

For j = 1 To 28

For i = 1 To 14

If inarr(i, j) = "" Then

Cells(i, j) = inarr(i - 2, j)

Cells(i + 1, j) = inarr(i - 1, j)

Exit For

End If

Next i

Next j

End Sub

 

[RAJESH1960]

Missed the A, Corrected the line inarr = Range("A1:AB14") but still getting the same error. I think it should be
for j = 15 to 28. Since I am cutting the values from columns 15 to 28 and pasting it to columns 1 to 14.

 

[offthelip]

If as variable is not declared it defaults to type variant. Anytime you load anything from the worksheet it has to be variant type. SO I never bother to declare that sort variable
this will get rid of the error. If you had tried the code without declaring anything it works fine.
Sub offthelip()

Dim inarr As Variant ' corrected this declaration
Dim j As Long, i As Long
inarr = Range("A1:AB14")
For j = 1 To 28
For i = 1 To 14
If inarr(i, j) = "" Then
Cells(i, j) = inarr(i - 2, j)
Cells(i + 1, j) = inarr(i - 1, j)
Exit For
End If
Next i
Next j

End Sub

 

[RAJESH1960]

offthelip. Your code is inter changing the values from 1 to 14 to 15 to 28. I just want the values of 15 to 28 to be cut and pasted to 1 to 14. The cells values of 15 to 28 when cut must be left blank. Then only the sum of total values of A16:AB16 will remain the same. Select and check row 17 =SUM(A2:A16).

 

[Fluff]

How about

Sub Rajesh()
   Dim Rng As Range
   For Each Rng In Range("O2:AB16").Columns
      With Rng.SpecialCells(xlConstants)
         Cells(16, .Offset(, -14).Column).End(xlUp).Offset(1).Resize(2).Value = .Offset(.Count - 2).Resize(2).Value
         .Offset(.Count - 2).Resize(2).Value = ""
      End With
   Next Rng
End Sub

 

[RAJESH1960]

How about

Sub Rajesh()
   Dim Rng As Range
   For Each Rng In Range("O2:AB16").Columns
      With Rng.SpecialCells(xlConstants)
         Cells(16, .Offset(, -14).Column).End(xlUp).Offset(1).Resize(2).Value = .Offset(.Count - 2).Resize(2).Value
         .Offset(.Count - 2).Resize(2).Value = ""
      End With
   Next Rng
End Sub

Fluff. Your code is working Perfect in my test sheet. Please let me check it my data base. By the way THANK YOU.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RAJESH1960]

Glad we could help & thanks for the feedback.

Fluff, As per your code, it is taking the values from the cell 15 & 16 and pasting it over the values of cell 1 & 2. As the range of values are different in each column, it has to take the values of the last 2 cells in each column from O to AD and paste it below the last empty cell in columns A to N. After the code runs the total sum value of all the columns should not change.
Line in your code "Cells(16, .Offset(, -14).... " needs to be altered I think. As the data is huge, I will not be able to share the sheet. But, I hope you will understand what I am trying to explain.