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, 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?
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.
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?