Okay, I'm lost as to what is happening.
I have two worksheets, Data and Inputs
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?
I have two worksheets, Data and Inputs
VBA Code:
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?