PDA

View Full Version : [SOLVED] Range A1 notation



simora
01-11-2020, 04:58 AM
Hi:
Is there a Excel Worksheet function that will allow me to convert this format $A$1 to A1.
OR VBA which will give the activeCell address with sheetname in that Range("A1") notation.

Paul_Hossler
01-11-2020, 07:10 AM
worksheet



=SUBSTITUTE(CELL("address",A1),"$","")


VBA



Activecell.Address(false,false)


Both return just "A1"

Edit: Ooops - missed the Sheet Name part :crying:

snb
01-11-2020, 07:30 AM
In Excel:


=ADDRESS(1;1;4)

In VBA


msgbox activecell.address(0,0)

p45cal
01-11-2020, 08:10 AM
VBA which will give the activeCell address with sheetname
MsgBox Split(ActiveCell.Address(0, 0, external:=True), "]")(1)

p45cal
01-11-2020, 10:08 AM
MsgBox Split(ActiveCell.Address(0, 0, external:=True), "]")(1)

That's a mess with sheet names with spaces in. This seems a bit better:
MsgBox Split(ActiveCell.Address(0, 0, external:=True), "[")(0) & Split(Split(ActiveCell.Address(0, 0, external:=True), "[")(1), "]")(1)
Gets even sillier if there's a single quote in the middle of the sheet name.

SamT
01-11-2020, 11:32 AM
MsgBox ActiveSheet.Name & "!" & ActiveCell.Address(0, 0)

MsgBox Range.Parent.Name & ".Range("" & Range.Address(0, 0) & "")"

simora
01-11-2020, 03:39 PM
Thanks Guys:

All great ideas & suggestions.