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.
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.
worksheet
VBA=SUBSTITUTE(CELL("address",A1),"$","")
Both return just "A1"Activecell.Address(false,false)
Edit: Ooops - missed the Sheet Name part
Last edited by Paul_Hossler; 01-11-2020 at 08:32 AM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
In Excel:
In VBAPHP Code:=ADDRESS(1;1;4)
PHP Code:msgbox activecell.address(0,0)
Originally Posted by simora
MsgBox Split(ActiveCell.Address(0, 0, external:=True), "]")(1)
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
That's a mess with sheet names with spaces in. This seems a bit better:Gets even sillier if there's a single quote in the middle of the sheet name.MsgBox Split(ActiveCell.Address(0, 0, external:=True), "[")(0) & Split(Split(ActiveCell.Address(0, 0, external:=True), "[")(1), "]")(1)
Last edited by p45cal; 01-11-2020 at 10:18 AM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
MsgBox ActiveSheet.Name & "!" & ActiveCell.Address(0, 0)MsgBox Range.Parent.Name & ".Range("" & Range.Address(0, 0) & "")"
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Thanks Guys:
All great ideas & suggestions.
Posting Permissions
Reply With Quote
