VBA Error Message - Method 'Range' of Object _Global' failed

Question

I have a code where I'm receiving a "'Range' of Object _Global' failed" error and I'm hoping someone can help as to why. I've looked online and everything seems to advise that I need to specific which sheet the code is referring to, which i've done using With and End With. Please see my code below;

With ThisWorkbook.Worksheets("Tables")
Range(StartingPoint, EndPoint).Copy
End With

However, it doesn't seem to be working. The variables specified above this code are as follows;

Dim ws7 As Worksheet
Set ws7 = ActiveSheet

Dim StartingPoint As Range
Dim EndPoint As Range
Set StartingPoint = Sheets("Tables").Range("C58:R58").Find(ws7.Range("C3"))
If Not StartingPoint Is Nothing Then
Set EndPoint = StartingPoint.Offset(10, 0)
End If

Any help and / or useful reading material would be much appreciated.

Thanks.

Where does the erroring part come in the code, do you check startpoint and endpoint before using? — Nathan_Sav
– Nathan_Sav, Commented Jan 15, 2016 at 9:23
It's the Range within the With / End With piece on the first set of code where's it falling over. — FISHED
– FISHED, Commented Jan 15, 2016 at 9:29
Just a quick comment: Besides from the full stop @Rory suggested in his answer, are you sure that StartingPoint is getting set to anything, because if it is not, then EndPoint will never get set, therefore failing. — Vulthil
– Vulthil, Commented Jan 15, 2016 at 9:49
I've ran the StartingPoint code entirely on its own, adding a copy request in, and it's doing exactly what it should be which indicates it's something to do with the EndPoint? — FISHED
– FISHED, Commented Jan 15, 2016 at 9:55

Rory · Accepted Answer · 2016-01-15 09:23:02Z

1

Your With statement is not currently doing anything as you haven't qualified the Range call - it needs to be:

With ThisWorkbook.Worksheets("Tables")
.Range(StartingPoint, EndPoint).Copy
End With

Note the full stop in .Range

Also, this assumes those two ranges are on the Tables sheet, or the code will fail.

answered Jan 15, 2016 at 9:23

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

FISHED Over a year ago

Thanks @Rory. I've done that, and am now getting an error saying "Application-defined or Object-defined error". Aren't StartingPoint / EndPoint being defined using the Dim / Set coding though?

Rory Over a year ago

Are those two ranges on the Tables sheet?

FISHED Over a year ago

Yes, the first range determines parameter 1, but it is also called on to determine parameter 2, the idea being that both together provide the range. So yes, both ranges are on the Tables sheet.

Rory Over a year ago

Are you certain of that? What does Msgbox StartingPoint.Parent.Name display?

FISHED Over a year ago

I was, not so much now though. But if I change it to specify the Tables sheets through Set StartingPoint = Sheets("Tables").Range("C58:R58").Find(ws7.Range("C3")) that should suffice shouldn't it?

|

Collectives™ on Stack Overflow

VBA Error Message - Method 'Range' of Object _Global' failed

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related