Code to enter formula w/ relative row ref?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

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
 
One way:

It's sometimes easier to use the FormulaR1C1 method:

Dim cell As Range
For Each cell In Range("C1:C20")
cell.FormulaR1C1 = _
"=LEFT(rc1&"".0.0."",FIND(""$""," & _
"SUBSTITUTE(rc1&"".0.0."",""."",""$"",3))-1)"
Next cell

but you can to it with A1-style references, too:

Dim cell As Range
For Each cell In Range("C1:C20")
With cell
.Formula = "=LEFT(A" & .Row & "&"".0.0.""," & _
"FIND(""$"",SUBSTITUTE(A" & .Row & _
"&"".0.0."",""."",""$"",3))-1)"
End With
Next cell

Note that you can't put this formula in "any cell of any row",
quite, since putting it in column A gives a circular reference.
 
see if you modify this to suit

Sub whatrow()
x = ActiveCell.Row
ActiveCell.Offset(, 1).Formula = "=left(a" & x & ",2)"
MsgBox x
End Sub
 
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
Click to expand...

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
 
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 said:
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
Click to expand...

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
Click to expand...
 
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 said:
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

row
and

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

ActiveCell.FormulaR1C1 = _
Click to expand...
"=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
Click to expand...
Click to expand...
 
Back
Top