Object Oriented Error -- Referencing Other Worksheets?

Question

I am using VBA to loop through a list of reference numbers ROID and return the correct full name (ROIDA) from a separate page. The page where the full name is located has the reference number in column D and the full name in column A.

Sub Main()
'Set variable types
    Dim WorksheetA As Excel.Worksheet
    Dim WorksheetB As Excel.Worksheet
    Dim ROID As Range, ROIDA As Range

    Set WorksheetA = ActiveWorkbook.Sheets("WorksheetA")
    Set WorksheetB = ActiveWorkbook.Sheets("Approval Flows")

'Replacing ROID #s with full Name
    'Define range of active requesting offices
    Set ROID = WorksheetA.Range(Range("A7"), Range("A7").End(xlDown))

    'Define range of attention lines and associated ROIDs
    Set ROIDA = WorksheetB.Range(Range("D7"), Range("D7").End(xlDown))

    'Loop through ROIDs and replace with ATTN line
    For Each ID In ROID
        Set Match = ROIDA.Find(ID)
        If Not Match Is Nothing Then
            ID = Match.Offset(0, -3)
            End If
        Next ID


End Sub

When I try to run the script, I receive an objected oriented error from this line:

Set ROIDA = WorksheetB.Range(Range("D7"), Range("D7").End(xlDown))

Is this because I'm working with multiple sheets? I am trying hard not to use the activate or select functions.

@Mat'sMug - quite, perhaps SO should just redirect all questions there. — SJR, Feb 16 '17 at 22:36

SJR · Accepted Answer · 2017-02-16T23:14:14.987

Yes, if A/B are not active when you run the macro. You need to fully qualify all ranges with sheets. Also better to go the end and work up than go Down in case you have nothing after A7.

With WorksheetA
    Set ROID = .Range(.Range("A7"), .Range("A7").End(xlDown))
'Or Set ROID = .Range(.Range("A7"), .Range("A" & Rows.count).End(xlup)) 
End With

'Define range of attention lines and associated ROIDs
With WorksheetB
    Set ROIDA = .Range(.Range("D7"), .Range("D7").End(xlDown))
End With

Object Oriented Error -- Referencing Other Worksheets?

1 Answers1