VBA Last Row and Column from different sheet in R1C1

Question

Find my code at the end of the question. I am trying to automate a daily process. Every day, a new row is added to the ITR file and I need to enter the new value of that column in another sheet. Is there a way I can create a macro which can select the last row of column 9? I tried to use the R1C1 format and used R[last_row]C9 but I have no idea if this would ever work. Any help? Thanks.

Sub Macro4()
'
' Macro4 Macro
'
'
    dim last_row As Long
    last_row = Cells.Find(What:="*", SearchDirection:=[ITR 21.10.2020.xlsx]).Row

    Range("E17").Select
    Selection.FormulaR1C1 = "='[ITR 21.10.2020.xlsx]Transactions'!R[last_row]C9"
End Sub

Answer 1

If the aim is to find last row in a particular column then you can use a normal formula like below (no VBA is needed).

=LOOKUP(2,1/('[ITR 21.10.2020.xlsx]Transactions'!I:I<>""),'[ITR 21.10.2020.xlsx]Transactions'!I:I)

Your code will require some tweaks to work as below.

Dim last_row As Long
last_row = Workbooks("ITR 21.10.2020.xlsx").Sheets("Transactions").Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
Range("E17").FormulaR1C1 = "='[ITR 21.10.2020.xlsx]Transactions'!R[" & last_row & "]C9"

Edit:

In such a case code will be needed to handle:

Dim last_row As Long
Dim strWbkName As String
strWbkName = "ITR " & Format(Date,"dd.mm.yyyy") & ".xlsx"
last_row = Workbooks(strWbkName).Sheets("Transactions").Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
Range("E17").FormulaR1C1 = "='[" & strWbkName & "]Transactions'!R[" & last_row & "]C9"