VBA entering a nested formula with text

[torquil]

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.

[SamT]

Assuming no empty rows in table
LastRow = .Range("A1").End(xlDown).Row + 2

[p45cal]

Sub Last_row_Add()
With Range("PerformanceTable")
'LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 2 'I think this is wrong
LastRow = .Cells(.Rows.Count, 1).Row + 2
End With
Worksheets("Performance Update").Range("A" & LastRow).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[" & 19 - LastRow & "]C[4])*Owned" & "Sh[Total Purchase Value])*100,2) & ""% change from your invested funds. should you wish to make changes to the investmenets please contact your account manager. """
End Sub
Could be simpler if the reference to Dashboard!E19 was absolute (Dashboard!$E$19) instead of relative.

[torquil]

Hi SamT,
doesnt work unfortunatley. BTW the table starts in A25 I tried amending to correct the cell referenece but its not work.

[torquil]

Dam it, p45cal...... you come to the rescue again!! thank you