Setting range on other sheet, based on abstract row number

Question

With a sub on sheet1, I need to get data from a range of cells (P.. to W..) from row X, where X is take from a reference cell on sheet2.

Dim final_range As Range
Dim ref_cell_range As Range
Dim ref_cell As Range

Set ref_cell_range = Worksheets("sheet2").Range("O9:O15")

For Each ref_cell In ref_cell_range
     Set final_range = Worksheets("sheet2").Range(Cells(ref_cell.Row, "P"), Cells(ref_cell.Row, "W"))
Next ref_cell

This code works if I leave out the 'Worksheets("sheet2").' part, but then it takes the cells from sheet1. If I run it like this (refencing the sheet to take the range from), I get an error ('Run-time error '1004': Application-defined or object-defined error')

Does anybody know what I might be doing wrong?

I doubt your code will work as is - ref_cell_range = Worksheets("sheet2").Range(O9:O15) should be Set ref_cell_range = Worksheets("sheet2").Range("O9:O15") - place the range reference in quotes. — Darren Bartrup-Cook
@DarrenBartrup-Cook; Sorry, my bad. In my actual code this is correct.. changed here.. When I step through, it really errors on the 'final_range' part.. — Goldberry

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2020-01-20T13:21:38

Your final_range reference is looking at two sheets.

This is looking at Sheet2:
Worksheets("sheet2").Range(

This is looking at whichever sheet is active as you haven't qualified the sheet for Cells to reference:
Cells(ref_cell.Row, "P"), Cells(ref_cell.Row, "W")

The full line should be:

Set final_range = Worksheets("sheet2").Range(Worksheets("sheet2").Cells(ref_cell.Row, "P"), Worksheets("sheet2").Cells(ref_cell.Row, "W"))

or using With...End With:

Sub Test()

    Dim final_range As Range
    Dim ref_cell_range As Range
    Dim ref_cell As Range

    With ThisWorkbook.Worksheets("sheet2")
        Set ref_cell_range = .Range("O9:O15")

        For Each ref_cell In ref_cell_range
             Set final_range = .Range(.Cells(ref_cell.Row, "P"), .Cells(ref_cell.Row, "W"))
        Next ref_cell

    End With

End Sub

Setting range on other sheet, based on abstract row number

1 Answers