Offset and for next

[voltrader]

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.

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!

 

[Rick Rothstein]

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.

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]

This will copy each cell in YourRange to the cell offset'ed 10 columns and 100 rows:

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]

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.

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.

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]

This will copy each cell in YourRange to the cell offset'ed 10 columns and 100 rows:

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.*

 

[ZAX]

you mean e.g.*

Hi again Dreadknight Nasus,
What * are you talking about?!
ZAX

 

[voltrader]

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....

 

[Dreadknight Nasus]

Hi again Dreadknight Nasus,
What * are you talking about?!
ZAX

E.g. vs. i.e. - Grammarist

 

[Rick Rothstein]

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)...

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

 

[ZAX]

E.g. vs. i.e. - Grammarist

Wow and thanx!
I learned something new and helpful that I will never forget...
ZAX