Set variable to active cell address

  • Thread starter Thread starter shelfish
  • Start date Start date
S

shelfish

I just need to set the value of a variable to the "A1" address of the
active cell. I feel like I've tried everything, but shouldn't this
work....

Dim rng as Range

Set rng = activecell [results in active cell's
value]
Set rng = activecell.address [throws error]

Thanks all,
Shelton.
 
rng = ActiveCell gives value
rng = ActiveCell.Address gives cell reference in A1 format
Set rng = ActiveCell is used to set the active cell as an object variable
range
then you cans do: x = rng.address to get a cell string in A1 format.

I get screwed around on these also. The best way to do it is to use a Range
or Cells reference if you can. If you have to have a variable, then don't
forget that it is a cell reference and you will need to use it like
Range(rng) when setting a range reference.
 
Hi,


Dim rngAddr As String
Dim rng As Range

Set rng = ActiveCell

rngAddr = rng.Address 'returns $A$1
rngAddr = rng.Address(0, 0) 'returns A1
rngAddr = rng.Address(0, 1) 'returns $A1
rngAddr = rng.Address(1, 0) 'returns A$1

'Use address like this

Range (rngAddr)
 
dim myAddr as string
myAddr = activecell.address(0,0)


I just need to set the value of a variable to the "A1" address of the
active cell. I feel like I've tried everything, but shouldn't this
work....

Dim rng as Range

Set rng = activecell [results in active cell's
value]
Set rng = activecell.address [throws error]

Thanks all,
Shelton.
Click to expand...
 
Hi,

Dim rngAddr As String
Dim rng As Range

Set rng = ActiveCell

rngAddr = rng.Address 'returns $A$1
rngAddr = rng.Address(0, 0) 'returns A1
rngAddr = rng.Address(0, 1) 'returns $A1
rngAddr = rng.Address(1, 0) 'returns A$1

'Use address like this

Range (rngAddr)

--
Regards,

OssieMac

shelfish said:
I just need to set the value of a variable to the "A1" address of the
active cell. I feel like I've tried everything, but shouldn't this
work....
Click to expand...
Dim rng as Range
Click to expand...
Set rng = activecell [results in active cell's
value]
Set rng = activecell.address [throws error]
Click to expand...
Thanks all,
Shelton.
Click to expand...
Click to expand...

Brilliant! Thanks for the help.
 
Very nice that you did this in one step. I knew it couldn't be a two-
step process. Thanks.
 
In attempting to execute the below lines of code (which I got from shellfish's posting above of Nov 21 2007), I get an error saying:


"Microsoft Visual Basic

Compile error:
Method or data member not found "


Dim rngAddr As String
Dim rng As Range

Set rng = ActiveCell
rngAddr = rng.Address

I am at a total loss to understand what I am doing wrong.
 
I managed to get the above to work by declaring:

DIM rng as Object

I suppose that between 2007 and 2019, the Excel VBA variable ActiveCell has become an Object.
 
Back
Top