combing OFFSET with LASTROW efficiently

[heathball]

Dim AC As Integer
Dim myc As range
Set myc = activecell
AC = activecell.Column

'if i have identified the active cell, then i can use the LR to autofill to last row like this

 LR = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
range(myc, Cells(LR, AC)) = "=IFERROR(IF(RC[-3]=1,RC[-1]-RC[-14],""#""),""#"")"

'BUT
'if i am using offset ...
'i cannot see a way to identify the cell or range to be able to use the "LR" autofill

myc.Offset(0, 12).Formula = "=IFERROR(IF(RC[-3]=1,RC[-1]-RC[-14],""#""),""#"")"

'unless i do this first

myc.Offset(0, 12).Select

'then call in a seperate autofill macro, which slows down the time of the long macro with 600,000 rows

Call autofill_downcol_lastrow_v2

(which operates from a selection and works fine, but a little slow)

'is there a way to combine OFFSET with "LASTROW type coding" which keeps it efficient, and not requiring multiple extra lines of code?
'which may look something like this.but not exactly this....

range((myc,Offset(0, 12)) Cells(LR, AC)) = "=IFERROR(IF(RC[-3]=1,RC[-1]-RC[-14],""#""),""#"")"

'or is it not possible?

 

[StephenCrump]

You could do something like this:

Dim FR As Long, LR As Long
Dim myc As Range

Set myc = ActiveCell
FR = myc.Row
LR = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

myc.Offset(, 12).Resize(LR - FR + 1).Formula = "=IFERROR(IF(RC[-3]=1,RC[-1]-RC[-14],""#""),""#"")"

But it's not clear why your formula is using column references relative to ActiveCell? This relies on ActiveCell being in the correct column. Why not just reference the correct columns directly?

 

[heathball]

You could do something like this:

Dim FR As Long, LR As Long
Dim myc As Range

Set myc = ActiveCell
FR = myc.Row
LR = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

myc.Offset(, 12).Resize(LR - FR + 1).Formula = "=IFERROR(IF(RC[-3]=1,RC[-1]-RC[-14],""#""),""#"")"

But it's not clear why your formula is using column references relative to ActiveCell? This relies on ActiveCell being in the correct column. Why not just reference the correct columns directly?

Thanks, Stephen, that seems to work very well. I will check out more about "RESIZE"

I am using relative references as it is for testing, which occurs anywhere on any sheet, and refers to the dynamic first starting column of any test.