Question

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

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.

友情链接