CODEWITHSUNDEEP
vbaexcelexcel-2010
Tom
Tom

Reputation: 31

Loop this Excel Macro?

I have this code below, and basically I want to able to make it do a whole range of cells from R16 to R200 and Range("G16").Select to G200.

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

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

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

Upvotes: 3

Views: 318

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 2

Related Questions