PDA

View Full Version : get cell address without the $'s



greymalkin
06-01-2007, 03:38 PM
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
06-01-2007, 04:00 PM
To remove the dollars use this syntax... Selection.Address(0, 0)

mdmackillop
06-01-2007, 04:27 PM
For more info, check the Address Property in VBA Help. Note that this is not installed by default.

greymalkin
06-01-2007, 07:31 PM
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
06-01-2007, 08:34 PM
Do a search thru the KB with the search word "progress" for some examples :)

Bob Phillips
06-02-2007, 03:15 AM
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 (http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm)

Paul_Hossler
06-02-2007, 08:56 PM
An altrenative approach could be to set a "bookmark" --


Dim rBackToHere as Range
....

Set rBackToHere = Selection
....

rBacktoHere.Select

Norie
06-03-2007, 08:36 AM
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
06-03-2007, 09:42 AM
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.) :yes and no need to Select it for most things :yes

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
06-03-2007, 09:51 AM
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
06-22-2017, 01:22 PM
how about just .address(false,false) which is the correct programmatic way to handle it

mdmackillop
06-22-2017, 01:26 PM
10 year old post