torquil
04-07-2020, 02:07 PM
Hi everyone,
I am trying to find the last row of a table adding 2 rows then pasting a formula into that cell.
The code i currenlty have to get the desired cell and enter something is:
Sub Last_row_Add()
With Range("PerformanceTable")
LastRow = .Cells(.Rows.count, 1).End(xlUp).Row + 2
End With
Worksheets("Performance Update").Range("A" & (LastRow + 2)).Select
ActiveCell.FormulaR1C1 = _
"=""This currently represents an overall $""&SUM(PerformanceTable[Profit/Loss])&"" ""&IF(SUM(PerformanceTable[Profit/Loss])<0,""loss"",""increase"")&"" in your portfolio which represents a ""&IF(SUM(PerformanceTable[Profit/Loss])>0,""+"",""-"")&"" ""&ROUND((SUM(PerformanceTable[Profit/Loss]))/SUMPRODUCT((OwnedSH[Share Owner]=Dashboard!R[-10]C[4])*OwnedSH[Total Purcha" & _
"se Value])*100,2)&""% change from your invested funds. should you wish to make changes to the investmenets please contact your account manager. """
End Sub
This works to enter the formula but it does not addapt when the table length changes.
Please advise how to make the find last row of table + 2 dynamic.
I am trying to find the last row of a table adding 2 rows then pasting a formula into that cell.
The code i currenlty have to get the desired cell and enter something is:
Sub Last_row_Add()
With Range("PerformanceTable")
LastRow = .Cells(.Rows.count, 1).End(xlUp).Row + 2
End With
Worksheets("Performance Update").Range("A" & (LastRow + 2)).Select
ActiveCell.FormulaR1C1 = _
"=""This currently represents an overall $""&SUM(PerformanceTable[Profit/Loss])&"" ""&IF(SUM(PerformanceTable[Profit/Loss])<0,""loss"",""increase"")&"" in your portfolio which represents a ""&IF(SUM(PerformanceTable[Profit/Loss])>0,""+"",""-"")&"" ""&ROUND((SUM(PerformanceTable[Profit/Loss]))/SUMPRODUCT((OwnedSH[Share Owner]=Dashboard!R[-10]C[4])*OwnedSH[Total Purcha" & _
"se Value])*100,2)&""% change from your invested funds. should you wish to make changes to the investmenets please contact your account manager. """
End Sub
This works to enter the formula but it does not addapt when the table length changes.
Please advise how to make the find last row of table + 2 dynamic.