Range of Worksheet Object Failed

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Okay, I'm lost as to what is happening.
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:

VBA Code:
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
 
Upvote 0
Solution
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:

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top