returning formula into a cell

[RiverGully]

I would like to return the results of a formula into a cell based on an
address that I've stored in code. Unfortunately, the result does not
provide me with a value from the formula.

Here is the code I've typed:

Dim LastLineAddress As String

LastLineAddress = ActiveCell.Address
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "+mid(" & (LastLineAddress) & ",5,2)"

End Sub

Here is the result in the spreadsheet:

Cell C149: 12345678
Cell C150
Cell C151 +mid($C$149,5,2)

What I was hoping for in Cell C151 was the value 56 to be displayed, not the
string +mid($C$149,5,2). Also I'd prefer not to have the absolute
references ($) in the formula.

Can you help me out? Many thanks.

 

[Bob Phillips]

You want the row?

Just use

ActiveCell.Offset(2, 0).Value = ActiveCell.Row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Thomlinson]

Try this...

Sub test()

Dim LastLineAddress As String

LastLineAddress = ActiveCell.Address
LastLineAddress = Replace(LastLineAddress, "$", "")
ActiveCell.Offset(2, 0).Formula = "=mid(" & (LastLineAddress) & ",5,2)"

End Sub

 

[Jim Thomlinson]

or if you want to be really concise you can do it all in just one line with
no variable...

ActiveCell.Offset(2, 0).Formula = "=mid(" & _
(Replace(ActiveCell.Address, "$", "")) & ",5,2)"

 

[Gary Keramidas]

does changing this line help?

ActiveCell.Formula = "=mid(" & (LastLineAddress) & ",5,2)"

 

[RiverGully]

Thank you very much - I'm over that hurdle now

 

[RiverGully]

Hi Jim,

Could you help with a related question to this solution?

I need to concatenate the letter 'n' to the result of the formula:
=+mid(C149.5,2)&"n"

I'm not succeeding in trying to add the &"n" at the end of the first formula
(keep getting debug error). Can you advise once more? Thank you again!

 

[Jim Thomlinson]

Try this...

ActiveCell.Offset(2, 0).Formula = "=mid(" & _
(Replace(ActiveCell.Address, "$", "")) & ",5,2) & ""n"""