Using the End.(xlUp) in a macro

[PeteD]

I have created a macro using the record macro function. However, I want to replace the portion where it selects the second cell by going up 4 rows and to the left 3 columns to use the End and Up key to find the top cell with a value, then go down one row before going to the left 3 columns.

When I try to replace the portion of the code with the End.(xlUp) the macro stops working.

Here is the code that was generated and it will work. However, the number of rows that I want to go up varies and this will always select the second cell by only going up 4 rows.


ActiveCell.FormulaR1C1 = "=+RC[-3]-R[-4]C[-3]"

ActiveCell.FormulaR1C1 = "=+RC[-3]-R[End.(xlUp)]C[-3]"

 

[Malcolm]

Using Offset is simpler

With ActiveCell
.Formula = "=" & .Offset(, -3).Address(1, 1) & "-" & .End(xlUp).Offset(, -3).Address(1, 1)
End With

 

[PeteD]

Using Offset is simpler

Thank You.