Switch from Cells to Range

Question

I see Range.AutoFill method (Excel) example:

Set sourceRange = Worksheets("Sheet1").Range("A1:A2") 
Set fillRange = Worksheets("Sheet1").Range("A1:A20") 
sourceRange.AutoFill Destination:=fillRange

I want to input a formula into the first empty column (always row 2) and then copy that down for all rows in the register (it's data copied from an outside source).

Sub SetNextEmptyFormula(strFormula As Variant)
  Dim ws As Worksheet
  Dim lCol As Long, lRow As Long

  Set ws = Workbooks("myworkbook.xlsm").Worksheets("register")

  ' Always row 2 -> lRow, always in lCol
  lCol = ws.Range("A1").End(xlToRight).Column
  lRow = ws.Range("A1").End(xlDown).Row
  ws.Cells(2, lCol + i).Value = CStr(strFormula)

  Set sourceRange = ws.Range(???)
  Set fillRange = ws.Range(???)
  sourceRange.AutoFill Destination:=fillRange
End Sub

Answer 1

I'm not sure how you're going to build your formula, but if you're just looking for help referencing these cells, this will do:

Option Explicit
Sub UseSub()
    
    Dim myFormula
    myFormula = "=ROW()"
    Call SetNextEmptyFormula(myFormula)
    
End Sub
Sub SetNextEmptyFormula(strFormula As Variant)

    Dim WS As Worksheet
    Dim lCol As Long, lRow As Long
    Dim fillRange As Range

    Set WS = Workbooks("myworkbook.xlsm").Worksheets("register")
   'Set WS = Workbooks("Book1.xlsm").Worksheets("register")
    
    With WS
        
        ' Always row 2 -> lRow, always in lCol
        lCol = .Range("A1").End(xlToRight).Column
        lRow = .Range("A1").End(xlDown).Row
        .Cells(2, lCol + 1).Formula = CStr(strFormula)
        
        'FillDown Only requires you reference the whole range Once, You only need "FillRange"
        'Set sourceRange = ws.Range(???)
        Set fillRange = .Range(.Cells(2, lCol + 1), .Cells(lRow, lCol + 1))
        fillRange.FillDown
    
    End With
    
End Sub