Define Object Variable

  • Thread starter Thread starter al
  • Start date Start date
A

al

Hi All,
This seems to be a so simple problem, but i can't figure it out.
Here is my code im using:

Sub test()
Dim r As Range
r = ActiveCell.Address
MsgBox r
Exit Sub

When i run that code, i get a Run-Time error '91':
Object Variable or With variable block not set

If i use:
Set r = ActiveCell.Address
instead of: r = ActiveCell.Address
then i get Type Mismatch error.

Can someone please help.

Cheers.
Albert
 
You're mixing types.

The Address property returns a string.
Use set when you want to reference an object.

This example may help.

Sub test()
Dim str As String, rng As Range

Set rng = ActiveCell
MsgBox rng.Address

str = ActiveCell.Address
MsgBox str

Set rng = Range(str)
MsgBox rng.Value
End Sub
 
Thank you both.
I did get confused Jan's reply, but i managed to work it out.
The joys of being thrown into the deep-end.

Again, thank you both for your help.

Cheers.
Al
 
Ron:
#3 <<below>> is returning a value, not the address property.
Was that intentional?
#1 and #2 are OK..

Set rng = Range(str)
MsgBox rng.Value

Thanks for all your help..
JM
 
Yes, intentional.

My point was that once you have the range object, you can return whichever
property you want.

I could have also written MsgBox rng.Address
 
Back
Top