vba simple range/object error

Question

So I thought I had an OK grasp of VBA but now this error is really bugging me and I can't figure out the problem. Here is an incredibly short subroutine (run from a module):

Sub test()
    Dim lr
    lr = Sheets(1).Range("a1", Cells(65, 1))
    MsgBox lr.Address
End Sub

For whatever reason this throws up an "application defined/object defined error"

can anyone explain?

Edit: This also throws up the same error:

MsgBox Sheets(1).Range("a1", Cells(65, 1)).Address

Edit2: I'm going to change my question a bit as I understand about the object thing but I what to use it with the "find" method. So here is the code:

Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)

In this case it tells me lookrange is causing a problem.

enderland · Accepted Answer · 2012-10-03 19:23:45Z

3

Ok. You've got several problems.

Option Explicit
sub test()
    Dim lr as Range
    set lr = Sheets(1).Range("a1", Cells(65, 1))
    MsgBox lr.Address
End Sub

First, as someone who is new to VBA, use Option Explicit nearly always at the tops of your modules so you are required to dimension your variables.

Second, you never declare lr as an object type - make sure you declare it as a type Range - you don't necessarily have to do this, however, it is good practice.

Third, you need to use set as others have indicated when assigning objects such as Range values or you will always get that error.

Onto the second part of your question.

MsgBox Sheets(1).Range("a1", Cells(65, 1)).Address

Change this to

With Sheets(1)
    MsgBox .Range(.Range("A1"), .Cells(65, 1)).Address
End With

If you are ever running this from a sheet different than Sheet1 you will get errors, because the Cells(65,1) reference will not be on Sheet1 - Excel will think you mean the current worksheet.

The third part

Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)

Has the same issue as second part. Also note it is entirely possible you will run errors if result cannot be found and the second statement above (the results of Find) will cause errors if Find is unsuccessful.

edited Oct 3, 2012 at 19:23

answered Oct 3, 2012 at 16:20

enderland

14.2k18 gold badges105 silver badges157 bronze badges

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

1 Comment

Daniel Over a year ago

Since the OP is relatively new with VBA(in theory), would you please suggest he use With Sheets(1) instead of using Sheets(1) 3 times in 1 line? It's much prettier and doesn't make me cringe.

KekuSemau · Accepted Answer · 2012-10-03 15:44:39Z

0

The point is that lr = Sheets(1).Range("a1", Cells(65, 1)) returns a range object.
Emphasis on object. So you need to use set at the start, as Daniel did.
As you assign that object without set, Excel uses the default property, which is Range.Value. So the assignment does not throw an error, only using lr as an object does.

answered Oct 3, 2012 at 15:44

KekuSemau

6,8434 gold badges27 silver badges37 bronze badges

1 Comment

KekuSemau Over a year ago

Your 'edit'-line should not throw an error, though, and it does not do so as I'm testing it.

Daniel · Accepted Answer · 2012-10-03 16:11:34Z

0

Probably your range definition is not correct...Not sure what you are trying to achieve there but sheets(1).range(myrange) returns a range object.

Sub tetPop()

    Call popAddress(10, 12)
    Call popAddress(14, 21)
    Call popAddress(11, 18)

End Sub

Sub popAddress(ByVal row As Integer, ByVal col As Integer)

    Dim lr As Range

    Set lr = Sheets(1).Range("a1", Cells(row, col))
    MsgBox lr.Address

    ' or better and direct
    MsgBox Sheets(1).Range("a1", Cells(row, col)).Address

End Sub

Would popup different address Cheers

Daniel

edited Oct 3, 2012 at 16:11

answered Oct 3, 2012 at 15:26

Daniel

112 bronze badges

2 Comments

harryg Over a year ago

Sure but the code above is not my programme, it is just the but that is causing a problem. I need to use the cells(x,x) method as the bottom of the range is not fixed and I intend to replace the 65 with a variable. The address method is also not relevant, I just don't know why it doesn't work, either as an object or just a range

scott Over a year ago

you can use this same notation using a variable for the 65 by using set lr = sheets(1).range("A1:A" & variablename)

Collectives™ on Stack Overflow

vba simple range/object error

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related