Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a concatenated formula into a cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a concatenated formula into a cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenated formula help | Excel Worksheet Functions | |||
Past Special Concatenated formula | Excel Discussion (Misc queries) | |||
cannot get concatenated formula to evaluate... | Excel Discussion (Misc queries) | |||
concatenated text to formula | Excel Discussion (Misc queries) | |||
Concatenated Formula | Excel Programming |