Well, I don't know exactly how it happened, but I stumbled onto the
solution. My code is simply
ActiveCell.FormulaR1C1 = _

"=LEFT(RC[-10]&"".0.0."",FIND(""$"",SUBSTITUTE(RC[-10]&"".0.0."",""."",""$""
,3))-1)"
ActiveCell.Calculate

which is exactly what you gave me that didn't work! (Exactly, except for
the cell references, which were adjusted to get me where I needed to go.)

Here's what happened: someone suggested I have the formula in a cell, the
have my code simply copy and Paste Special Formula, and it would grab the
correct reference. Due to the way I lay out this file, I couldn't have the
formula in there first; it would have to be typed in - which was my problem!
So I tried a previous trick - while the recorder is running, type it in as
text, then click in the formula bar and enter an = sign to make it a
formula. Voila - it came out as above. Since I'm on manual calculation
(which I didn't think of before and might well have been why your answer
"didn't work" for me!!), I added the Calculate, and it all works
beautifully.

Thanks so much for responding to my post.

Ed

"Ed" wrote in message
...
Thanks for replying. But it didn't work. I have my data in column A (A1
is
a header, so data starts in A2). I entered the formula in C9, then turned
on the recorder with relative reference, clicked in the formula bar and
hit
enter. The recorder captured this:

ActiveCell.FormulaR1C1 = _


"=LEFT(R[-7]C[-2]&"".0.0."",FIND(""$"",SUBSTITUTE(R[-7]C[-2]&"".0.0."","".""
,""$"",3))-1)"
ActiveCell.Offset(1, 0).Range("A1").Select

The formula in C9 still points to A2.

Did I do something wrong?

Ed

"Father Guido" wrote in message
...
On Tue, 30 Sep 2003 09:36:00 -0700, "Ed"
wrote:

I have a formula (courtesy of J.E. McGimpsey - thank you!!) as
follows:
=LEFT(A1&".0.0.",FIND("$",SUBSTITUTE(A1&".0.0.",". ","$",3))-1)

I'm trying to create a macro that will enter this in any cell of any
row
and
pick up the ActiveCell row reference. So A1 would be "A &
ActiveCellRow".
I've tried several ways, but get lost in parenthesis, quotes and
double-quotes, and VBA Expected End of Statement errors.

Any assistance is greatly appreciated.

Ed

It depends where the formula is to be entered, for instance, if I enter
your formula in C1 I'd get this...

ActiveCell.FormulaR1C1 = _



"=LEFT(RC[-2]&"".0.0."",FIND(""$"",SUBSTITUTE(RC[-2]&"".0.0."",""."",""$"",3
))-1)"

Just enter the formula, as you stated, in whatever cell it will be
applied to. Turn on the macro recorder, click on relative referencing,
click in the formula on the formula bar, hit enter and then stop the
macro recorder. Copy the formula from the recorded macro to wherever you
need it.

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good