Loop Macro

[paul_sykes00]

Hi all,

I have the below macro. I am trying to get it to loop. Where each time the R[-1] part increases by 1 and keeps repeating until it finds an empty cell

Sub Test
Sheets("Sheet1").Select
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Sheet2!R[-1]C[-2]"
Range("C4").Select

Call MACROX
End Sub

Hope that makes sense. Thanks in advance

 

[nemmi69]

This work?

Sub Test()
Dim Aloop As Integer
Dim LastRowNo As Long
Dim RowInc As Integer


Sheets("Sheet1").Select
LastRowNo = Sheets("Sheet1").Range("C65536").End(xlUp).Row
RowInc = -1


For Aloop = 3 To LastRowNo
    Range("C" & Aloop).Select
    If Trim(Range("C" & Aloop).Value) = "" Then Exit For
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Sheet2!R[" & RowInc & "]C[-2]"
    RowInc= RowInc-1
Next Aloop
Call MACROX
End Sub

 

[paul_sykes00]

Thanks for your response

Sorry. I should have probably been clearer.

1st Loop
Take the value from cell A2 sheet 2, paste it into cell C3 sheet 3, then run the macro

2nd Loop
Take the value from cell A3 sheet 2, paste it into cell C3 sheet 3, then run the macro

3rd Loop
Take the value from cell A4 sheet 2, paste it into cell C3 sheet 3, then run the macro

and so on until value taken from sheet 2 is blank

 

[KolGuyXcel]

Maybe

Sub Test
dim i as long
    for i = 2 to sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    sheets("Sheet3").cells(3,3).value2 = sheets("Sheet2").cells(i,1).value2
    Call MACROX
    next
End Sub

 

[paul_sykes00]

Perfect. Thanks,