Define Object Variable
 

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

Define Object Variable
 

Hi Al,

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


Make that:

Set r = ActiveCell.Address

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Define Object Variable
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"al" wrote in message
...
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

Define Object Variable
 

Typo?
Are you sure you dont mean:
Set r = ActiveCell

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jan Karel Pieterse" wrote in message
...
Hi Al,

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


Make that:

Set r = ActiveCell.Address

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Define Object Variable
 

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


"al" wrote in message
...
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

Define Object Variable
 

Hi Rob,

Are you sure you dont mean:
Set r = ActiveCell


Of course I did, who added that silly .address to it ?

<bg

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Define Object Variable
 

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


"Rob van Gelder" wrote in message
...
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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"al" wrote in message
...
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

Define Object Variable
 

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jim May" wrote in message
news:J3W9d.34289$a85.26039@fed1read04...
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


"Rob van Gelder" wrote in message
...
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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"al" wrote in message
...
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