Loop this Excel Macro ?

Question

Hi Guys,

I have this macro

Sub Search()

Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(R16C6,RC[4])),RC[2],"""")" ' Edit RXX value
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H385")
Range("H2:H385").Select
Range("G16").Select ' Edit GXX value
ActiveCell.FormulaR1C1 = "=SpecialConcatenate(C[1])"
Range("G11").Select
Application.Run "Test.xlsm!CopyPaste"
Range("H2").Select

End Sub

And basically I want to able to make it do a whole range of cells from

R16 ---> R200

and

Range("G16").Select --> G200

so i.e. when it changes to R17 then G16 becomes 17 ? when R18 then G18 etc

So the whole function runs changing R2 --> R3 and G2 --> G3, starts again and changes R3 --> R4, G3 --> G4 etc

Love any help anyone can provide!

Answer 1

Sub Search2()

    With Range("h2:h385")
        .FormulaR1C1 = "=IF(ISNUMBER(SEARCH(R[14]C6,RC[4])),RC[2],"""")"
    End With

    Range("G16").FormulaR1C1 = "=SpecialConcatenate(C[1])"
    Application.Run "Test.xlsm!CopyPaste"

End Sub

If you put the R number in brackets, it makes it relative to whatever cell you're in. So R[14] will point to row 16 when you're in row 2 and it will point to row 214 when you're in row 200.