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!

 

[voltrader]

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

Hey Zax, Thanks for the reply.
I'm still looking through the code, but it seems as if it is offsetting the first line in the range 100 rows down & than offsetting the next line 101 rows instead of 201 rows down. & is there a way to expand it from "For each cell in your Range" to multiple cells per row. To copy either 2 columns, or 4 columns at a time?

 

[Rick Rothstein]

Hey Zax, Thanks for the reply.
I'm still looking through the code, but it seems as if it is offsetting the first line in the range 100 rows down & than offsetting the next line 101 rows instead of 201 rows down. & is there a way to expand it from "For each cell in your Range" to multiple cells per row. To copy either 2 columns, or 4 columns at a time?

Did you get a chance to try my code in Message #9 yet (I'm interested to know if it works or not)?

 

[voltrader]

Hey Rick, That works awesome!
I will work over the weekend to integrate my code to your sub.

Have a nice weekend!

 

[voltrader]

Hey Rick, Your code works great!

I'm kinda confused how it works, & I will def. try to extrapolate it over the weekend.

Thanks again!

 

[voltrader]

Hey Rick,

I am trying to add either paste special or .formula=.value to the line

Cells(StartRow, DataCol).Resize(, ColumnCount).Copy Cells(R, DataCol).Offset(RowOffset * (R - StartRow), ColOffset)

I cant seem to get the right object & syntax.

Thanks,