return a concatenated formula into a cell

[RiverGully]

I need to concatenate the letter 'n' after a formula that was returned to a
cell.


Here is the code I've typed (as helped from Jim Thomlinson):
Sub test()

Dim LastLineAddress As String

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

End Sub



Here is the result in the spreadsheet:

Cell C149: 12345678
Cell C150
Cell C151 Formula =+mid(C149,5,2) with resultant value of 56 shown in
cell

I now need the resultant formula to read =+mid(C149,5,2)&"n" with the
resultant value being 56n.

I've tried to edit the VBA code to add this &"n", but keep getting debug
error.

Can you advise. Thank you again!

 

[Dave Peterson]

One way:

Option Explicit
Sub test2()

Dim LastLineAddress As String

'this will remove the $'s from the .address
LastLineAddress = ActiveCell.Address(false, false)

ActiveCell.Offset(2, 0).Formula _
= "=mid(" & LastLineAddress & ",5,2)&""n"""
End Sub

This is one of those times that working in R1C1 reference style makes it easier.

Option Explicit
Sub test2()
ActiveCell.Offset(2, 0).FormulaR1C1 = "=mid(r[-2]c,5,2)&""n"""
End Sub

In this expression: r[-2]c
r[-2] says to use two rows up from the cell getting the formula
c says to use the same column

 

[RiverGully]

Thank you Dave. This has been very helpful to me.

One way:

Option Explicit
Sub test2()

Dim LastLineAddress As String

'this will remove the $'s from the .address
LastLineAddress = ActiveCell.Address(false, false)

ActiveCell.Offset(2, 0).Formula _
= "=mid(" & LastLineAddress & ",5,2)&""n"""
End Sub

This is one of those times that working in R1C1 reference style makes it easier.

Option Explicit
Sub test2()
ActiveCell.Offset(2, 0).FormulaR1C1 = "=mid(r[-2]c,5,2)&""n"""
End Sub

In this expression: r[-2]c
r[-2] says to use two rows up from the cell getting the formula
c says to use the same column

I need to concatenate the letter 'n' after a formula that was returned to a
cell.

Here is the code I've typed (as helped from Jim Thomlinson):
Sub test()

Dim LastLineAddress As String

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

End Sub

Here is the result in the spreadsheet:

Cell C149: 12345678
Cell C150
Cell C151 Formula =+mid(C149,5,2) with resultant value of 56 shown in
cell

I now need the resultant formula to read =+mid(C149,5,2)&"n" with the
resultant value being 56n.

I've tried to edit the VBA code to add this &"n", but keep getting debug
error.

Can you advise. Thank you again!