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:
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.