Loop/Repeat Macro

[c0087]

I'm trying to automatically run this so after it finishes copying Sheet "Corp" to a new sheet, it will change the R[-2] to R[-3], then copy/paste "Corp", change to R[-4], etc etc. The final run should be R[-1465].

Sub Macro1()
'
' Macro1 Macro
'

'
  
    ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N2]-R[-2]C[-8])"
    Range("L1468").Select
    Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
    Range("L2:L1468").Select
    Sheets("Corp").Select
    Cells.Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[c0087]

bump

 

[CephasOz]

Speaking for myself only, I don't find it clear from your existing code or comments what it is that you want to happen. Can you give any further information, or perhaps examples?

 

[c0087]

Speaking for myself only, I don't find it clear from your existing code or comments what it is that you want to happen. Can you give any further information, or perhaps examples?

 

[myall_blues]

As @CephasOz said, it's not entirely clear what you want, but perhaps a loop that replaces the R[-2] in the first line is what you are seeking? If so you could do this with a For loop.

Sub Macro1()
'
' Macro1 Macro
'
    Dim i as long
'
 
    For i = -2 to -1465 Step -1
         ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N2]-R[i]C[-8])"  '<= Replace -2 with i in this line
         Range("L1468").Select
         Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
         Range("L2:L1468").Select
         Sheets("Corp").Select
         Cells.Select
         Selection.Copy
         Sheets.Add After:=ActiveSheet
         Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
             xlNone, SkipBlanks:=False, Transpose:=False
    Next i
End Sub

As an aside, I gather you recorded this macro. As a result quite a bit of the code is redundant. For example, it is usually not necessary to use .Select in VBA code as the objects can be combined. So for example the lines:
Sheets("Corp").Select
Cells.Select
Selection.Copy
can be combined into one line:
Sheets("Corp").Cells.Copy

Regards

Murray

 

[c0087]

Speaking for myself only, I don't find it clear from your existing code or comments what it is that you want to happen. Can you give any further information, or perhaps examples?

As @CephasOz said, it's not entirely clear what you want, but perhaps a loop that replaces the R[-2] in the first line is what you are seeking? If so you could do this with a For loop.

Sub Macro1()
'
' Macro1 Macro
'
    Dim i as long
'
 
    For i = -2 to -1465 Step -1
         ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N2]-R[i]C[-8])"  '<= Replace -2 with i in this line
         Range("L1468").Select
         Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
         Range("L2:L1468").Select
         Sheets("Corp").Select
         Cells.Select
         Selection.Copy
         Sheets.Add After:=ActiveSheet
         Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
             xlNone, SkipBlanks:=False, Transpose:=False
    Next i
End Sub

As an aside, I gather you recorded this macro. As a result quite a bit of the code is redundant. For example, it is usually not necessary to use .Select in VBA code as the objects can be combined. So for example the lines:
Sheets("Corp").Select
Cells.Select
Selection.Copy
can be combined into one line:
Sheets("Corp").Cells.Copy

Regards

Murray

Yes I did record it, and yes this is exactly what I want done, however I'm getting a "run-time error 1004" on the ActiveCell line

 

[c0087]

It seems to be an error with FormulaR1C1 ... so if I insert the actual formula

=ABS(CD_4[@N2]-D1467

how can apply your solution? in this case it would need to go from =ABS(CD_4[@N2]-D1467 to =ABS(CD_4[@N2]-D1466 to =ABS(CD_4[@N2]-D1465 , etc all the way to =ABS(CD_4[@N2]-D2

 

[myall_blues]

My apologies I missed this. 1004 errors can be hard to track down. It should work if you do: (remeber to include the dim statement)

dim i as long
For i=2 to 1467
ActiveCell.Formula = "=ABS(CD_4[@N2]-D" & i

 

[kvsrinivasamurthy]

Try

Sub Macro1()
Dim T&
    For T = 2 To 1465
    ActiveCell.FormulaR1C1 = "=ABS(CD_4[@N2]-R[-" & T & "]C[-8])"
    Range("L1468").Select
    Selection.AutoFill Destination:=Range("L2:L1468"), Type:=xlFillDefault
    Range("L2:L1468").Select
    Sheets("Corp").Select
    Cells.Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Next T
End Sub

 

[c0087]

My apologies I missed this. 1004 errors can be hard to track down. It should work if you do: (remeber to include the dim statement)

dim i as long
For i=2 to 1467
ActiveCell.Formula = "=ABS(CD_4[@N2]-D" & i

Unfortunately I'm still getting the same 1004 error even with this change