vba simple range/object error

问题:

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.

回答1:

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.

回答2:

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.

回答3:

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