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.

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)

"RiverGully" wrote in message
...
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.

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

--
HTH...

Jim Thomlinson


"RiverGully" wrote:

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.

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)"

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"RiverGully" wrote:

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.

does changing this line help?

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


Gary


"RiverGully" wrote in message
...
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.

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

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"RiverGully" wrote:

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.

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" wrote:

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

--
HTH...

Jim Thomlinson


"RiverGully" wrote:

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.

Try this...

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

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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)"

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"RiverGully" wrote:

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.