Excel Shortcut Key for Name Box

[FlowerGirl]

I would like to be able to capture the cell address in the Name Box and use it as the beginning address of a range I want to copy. How do I get to the Name Box to copy that information?

 

[Kenneth Hobson]

For a vba macro or in interactive session?

You can click in it and copy it.

 

[FlowerGirl]

I do not know how to do macros. What I want to do is program the shortcut key strokes into an AutoMate program.

 

[bk]

Hey Flower Girl, what about the CELL formula?

I'm not totally sure I understand what you want, is that on the right path?

 

[FlowerGirl]

bk,
That would work if I knew how to refer to the Name Box. =Cell(Name Box) doesn't work. As soon as I type = in a cell, the Name Box changes to SUM and if I click on it, I get the Function Arguments dialog box.

 

[bk]

Can you give some example data?

Exactly what's in a sample cell.

Exactly what you want to capture.

And what you mean by "capture"? Capture where, to do what with next

Also what do you mean by "name box", the little place where it puts the named range, cell reference or whatever that's active? Usually in upper left corner above A1?

Pardon me for being a little slow, but I can actually be useful sometimes once I grasp what somebody's wanting.

 

[FlowerGirl]

bk, yes, the "cell" that is directly above A1. What I want to do is:
Ctrl+F a specific text string. The cell number of that text string in now in the Name Box. Then, I want to be able to go to the Name Box and copy (capture) that cell address (for example: M72) so I can later use it as the beginning address of a range of data I want to copy.
Did that make sense?

 

[bk]

delete

 

[bk]

It made a little more.

The "name box" is nothing more than the address/named-range of the active cell really. So, unless you are absolutely certain that you MUST have the name box, I think we can get you what you want a different way.

This can be done probably more than one way, but via a Macro that uses Target or ActiveCell would probably be the best.

So, assuming that "captures" it properly, what, when and how are you going to use it? That may influence exactly how we get it or what we do with it.

 

[FlowerGirl]

I think I may have a way to get the info I need. After I do my Find, I'm in the cell I want the address of. If I do =Column(), put that information into a variable, move right one cell, do Row(), put that infor into another variable, then do =Address(var1,var2) and put that into a third variable, I get an answer I can work with. I can then close without saving, open back up and goto the address in Var3. I can get the ending location the same way.
Did I mention I don't know how to do macros yet?