get cell address without the $'s

[greymalkin]

Hello All,

I've been programming for a few years now but have never used Excel VBA until a couple days ago when I was asked to automate some processes.

I'm trying to create a string variable that contains the name of the cell (eg. A4, AB456, etc.) I will then call on it later to go back to that point
using Range(variableName).Select

I have found .Address, but when I call it instead of giving me A4 it gives me $A$4.

So I have 2 questions

1) Is there some other function I can call that will give me the address I'm wanting?
2) If not, is there a way I can manipulate the string so that I can remove the $'s? I have tried replace but I'm afraid I keep getting the syntax wrong.

thanks for your help!

[johnske]

To remove the dollars use this syntax... Selection.Address(0, 0)

[mdmackillop]

For more info, check the Address Property in VBA Help. Note that this is not installed by default.

[greymalkin]

fantastic!

thanks for the quick replies.

this is exactly what I needed.

The only other thing I'm not sure of right now is making some sort of progress form..I was wanting to make a form that was shown as soon as the macro started with hidden labels that would appear at different points in the macro execution such as:

querying database...
updating spreadsheet with document types...
etc..

When I made a form and used the form1.Show command, it halted the macro operations, however...is there a property of the form that will let it show while the macro continues to run, updating it as I hit key points in the macro (by simply making label1.Visible=True, label2.Visible=True..etc)

thanks again!

[johnske]

Do a search thru the KB with the search word "progress" for some examples

[Bob Phillips]

The only other thing I'm not sure of right now is making some sort of progress form..I was wanting to make a form that was shown as soon as the macro started with hidden labels that would appear at different points in the macro execution such as:

querying database...
updating spreadsheet with document types...
etc..

When I made a form and used the form1.Show command, it halted the macro operations, however...is there a property of the form that will let it show while the macro continues to run, updating it as I hit key points in the macro (by simply making label1.Visible=True, label2.Visible=True..etc)

thanks again!

When using a progress form, one appraoch is to initiate the working macro from within the userform, aomething akin to

Show form
Setup PB
Start macro
do a bit
update form PB
do some more
update form PB
etc.
End macro
Unload form

or else use a modeless form and DoEvents, nice example here

[Paul_Hossler]

An altrenative approach could be to set a "bookmark" --


Dim rBackToHere as Range
....

Set rBackToHere = Selection
....

rBacktoHere.Select

[Norie]

Why do you need to 'go back'?

Generally you don't need to select/activate, so in theory you should never go away.

PS What's the problem with the $ anway?

[Paul_Hossler]

Norie --

The OP's example was

"I'm trying to create a string variable that contains the name of the cell (eg. A4, AB456, etc.) I will then call on it later to go back to that point
using Range(variableName).Select"

so I was trying to stay within the intent of the question.

Nothing wrong with absolute references ($A$4, etc.) and no need to Select it for most things

In addition to saving the address in a string and then using Range (string) to Select it, you could also save the cell as a range object and manipulate it directly. -- Just a thought about another way

[Norie]

Paul

I'm not contradicting your's or anyone else's suggestions, I'm just trying to point that what the OP is doing probably isn't needed in the first place.

If something isn't actually needed I would suggest it's not actually done.

[pmpatane]

how about just .address(false,false) which is the correct programmatic way to handle it

[mdmackillop]

10 year old post