Range of Worksheet Object Failed

[GeeWhiz7]

Okay, I'm lost as to what is happening.
I have two worksheets, Data and Inputs

Option Explicit

'*************************************************************************
'*                              Transform Data                           *
'*************************************************************************
Sub TransformData()
    Dim a, b As Variant
    
    Dim iSh As Worksheet
    Dim oSh As Worksheet
    
    Dim lr As Long, lc As Long
    
    Set iSh = Worksheets("Inputs")
    Set oSh = Worksheets("Data")
    
    lr = iSh.Cells(Rows.Count, 1).End(xlUp).Row
    lc = iSh.Cells(1, Columns.Count).End(xlToLeft).Column
    
    a = iSh.Range(Cells(2, 2), Cells(lr, lc)).Value 'Gets input data
    
' Main Code here takes a array and does some stuff to get b array.
    
    oSh.Range("B2").Resize(UBound(b, 1), x).Value = b
     
End Sub

What I don't understand is when I run the macro while the Inputs worksheet is active, everything runs fine....ie I am on the iSh object worksheet.
When I am elsewhere like in the Data worksheet, then I get a 'Range of object _worksheet failed" error. I thought setting iSh and oSh would fix the issue.

How can I modify the code to make sure that no matter what worksheet I'm on, it always uses the iSh (inputs) and oSh (Data) from and to the correct places?

 

[StephenCrump]

The problem is your two unqualified Cells references - they will refer to the ActiveSheet, which may not be the same as ish.

a = iSh.Range(Cells(2, 2), Cells(lr, lc)).Value 'Gets input data

Here's one way you could code this:

With ish
    lr = .Cells(Rows.count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.count).End(xlToLeft).Column
    a = .Range(.Cells(2, 2), .Cells(lr, lc)).Value
End With

 

[GeeWhiz7]

The problem is your two unqualified Cells references - they will refer to the ActiveSheet, which may not be the same as ish.

a = iSh.Range(Cells(2, 2), Cells(lr, lc)).Value 'Gets input data

Here's one way you could code this:

With ish
    lr = .Cells(Rows.count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.count).End(xlToLeft).Column
    a = .Range(.Cells(2, 2), .Cells(lr, lc)).Value
End With

Interesting and that works. I did not know that despite the iSh.Range part, the cells would still refer to the active sheet. Thanks for that learning!

 

[StephenCrump]

No problem. I'm glad we could help.