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

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

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

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

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

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

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

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