'for' Loop

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
212345678910111213141512345678910111213
32345678910111213141516234567891011121314
434568910111213141516173456789101112131415
5456791112141618456789111213141516
6567810121315567891012131516
7791479101417
8810158101115
99111691116
1010171017
1111181118
1212191219
13
14
15
16
171577256011402450154336539584566157725605640245015446658758
181519
19
Original
Cell Formulas
RangeFormula
A17:AB17B17=SUM(B2:B16)
A18A18=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1D-1D-2D-3D-4D-5D-6D-7D-8D-9D-10D-11D-12D-13D-14D-15D-16D-17D-18D-19D-20D-21D-22D-23D-24D-25D-26D-27D-28
212345678910111213141512345678910111213
3234567891011121314151623456791011121314
434568891011121314151634567810111314
54567999111218141416155671215
6567810101213191515161667813
717711981412177914
818812109151381015
99111691116
101010171017
11111118
121219
13411
14512
15
1650864881284043671777090689176316542560182172711721365427
171519
181519
190Differnce must be 0
After Code
Cell Formulas
RangeFormula
A16:AB16B16=SUM(B2:B15)
A17A17=SUM(A16:AB16)
A18A18=Original!A18
A19A19=A17-A18
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,279
Office Version
  1. 2010
Platform
  1. Windows
try this code:
VBA 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

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
try this code:
VBA 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

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
2,279
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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
 
Solution

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
856
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,960
Messages
5,767,336
Members
425,404
Latest member
Bairkus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top