Offset and for next

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
Hey all,

I have a code that I am trying to expand to a range of about 20 rows by 2 columns. I would like for the macro to loop (?) through the entire range and offset each cells of the range 10 columns right, & 100 rows down.

Code:
Public Sub lmtpastespecial()
    ActiveSheet.range("a4:b4").Copy
    range("h4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveSheet.range("c4:d4").Copy
    range("l4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.range("f4:n4").Insert _
    shift:=xlDown
    range("n5").FormulaR1C1 = "=RC[-5]/RC[-1]"
        NumberFormat = "0.0000"
    ActiveSheet.range("f55:n55").ClearContents
End Sub
Any thoughts/opinions let me know!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
I have a code that I am trying to expand to a range of about 20 rows by 2 columns. I would like for the macro to loop (?) through the entire range and offset each cells of the range 10 columns right, & 100 rows down.

Code:
Public Sub lmtpastespecial()
    ActiveSheet.range("a4:b4").Copy
    range("h4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveSheet.range("c4:d4").Copy
    range("l4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.range("f4:n4").Insert _
    shift:=xlDown
    range("n5").FormulaR1C1 = "=RC[-5]/RC[-1]"
        NumberFormat = "0.0000"
    ActiveSheet.range("f55:n55").ClearContents
End Sub
I have looked at your code and your introductory description and cannot figure out exactly what your existing set up is nor what you want from it once the code is finished executing. Can you explain in more detail what your existing data looks like and what you ultimately want from it? When doing so, pretend you are explaining it to someone not familiar with any part of your process (because that is the situation for the volunteers here who you are asking to help you).
 

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
This will copy each cell in YourRange to the cell offset'ed 10 columns and 100 rows:
Rich (BB code):
Sub LoopThroughRangeAndOffset()
Dim YourRange As Range
Set YourRange = Range("?") 'Type your range's address here i.e Range("A2:B21")
For Each Cell In YourRange
    Cell.Copy Cell.Offset(100, 10)
    Application.CutCopyMode = False
Next
End Sub
ZAX
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,926
Office Version
2013
Platform
Windows
Hey all,

I have a code that I am trying to expand to a range of about 20 rows by 2 columns. I would like for the macro to loop (?) through the entire range and offset each cells of the range 10 columns right, & 100 rows down.

Code:
Public Sub lmtpastespecial()
    ActiveSheet.range("a4:b4").Copy
    range("h4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveSheet.range("c4:d4").Copy
    range("l4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.range("f4:n4").Insert _
    shift:=xlDown
    range("n5").FormulaR1C1 = "=RC[-5]/RC[-1]"
        NumberFormat = "0.0000"
    ActiveSheet.range("f55:n55").ClearContents
End Sub
Any thoughts/opinions let me know!
Why do it one cell at a time.
Code:
Sub moveRange()
'Select the range you want to move or copy.
Selection.Copy Selection.Offset(100, 10)
Application.CutCopyMode = False
End Sub
If you want to move it, use Cut instead of Copy.
 

Dreadknight Nasus

Board Regular
Joined
Jun 3, 2013
Messages
241
This will copy each cell in YourRange to the cell offset'ed 10 columns and 100 rows:
Rich (BB code):
Sub LoopThroughRangeAndOffset()
Dim YourRange As Range
Set YourRange = Range("?") 'Type your range's address here i.e Range("A2:B21")
For Each Cell In YourRange
    Cell.Copy Cell.Offset(100, 10)
    Application.CutCopyMode = False
Next
End Sub
ZAX
you mean e.g.*
 

voltrader

Board Regular
Joined
Dec 17, 2009
Messages
56
I have looked at your code and your introductory description and cannot figure out exactly what your existing set up is nor what you want from it once the code is finished executing. Can you explain in more detail what your existing data looks like and what you ultimately want from it? When doing so, pretend you are explaining it to someone not familiar with any part of your process (because that is the situation for the volunteers here who you are asking to help you).

Hey Rick, Thanks for the reply. I have a range of lets say 20 data points down and 4 data points across.
  1. First row of data I want to offset 10 columns right.
  2. Than I have an external RTD filling data 100 rows down.
  3. Second data point from the original range, should be offset 102 rows down, 10 columns right.
  4. Third data point from the original range should be offset 203 rows down 10 columns across, etc....
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,907
Office Version
2010
Platform
Windows
Hey Rick, Thanks for the reply. I have a range of lets say 20 data points down and 4 data points across.
  1. First row of data I want to offset 10 columns right.
  2. Than I have an external RTD filling data 100 rows down.
  3. Second data point from the original range, should be offset 102 rows down, 10 columns right.
  4. Third data point from the original range should be offset 203 rows down 10 columns across, etc....
I think this code will do what you asked (note the Const statement assignments control the assumed layout of your data)...
Rich (BB code):
Sub MoveAndOffsetDataRows()
  Dim R As Long, LastRow As Long
  Const DataCol As String = "A"
  Const StartRow As Long = 2
  Const ColumnCount As Long = 4
  Const ColOffset As Long = 10
  Const RowOffset As Long = 100
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  For R = StartRow To LastRow
    Cells(R, DataCol).Resize(, ColumnCount).Copy Cells(R, DataCol).Offset(RowOffset * (R - StartRow), ColOffset)
  Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,029
Messages
5,466,116
Members
406,467
Latest member
bpat83

This Week's Hot Topics

Top