return a concatenated formula into a cell
Thank you Dave. This has been very helpful to me.
"Dave Peterson" wrote:
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 wrote:
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
|