I need to replace a square bracketed range with a variable range

I have a macro that puts s formula into a two-dimensional range and correctly adds the relative addresses. However the range is "hard-coded" and I need to be able to define the depth of the range programicly. My code uses square brackets to set the range and it seems that you can't use a variable inside square brackets (or at least I can't figure out how to do so)

I need the code to use a variable to define the bottom right hand corner of the range. I Ideas would be most helpful.

I want to replace [T3:DX321].Formula with something like ["T3:DX" & Lastrow].Formula...

NOTE the three named ranges in the code each refer to a single column of data.


    Dim Lastrow As Long
    Lastrow = BillSch.Range("S" & Rows.Count).End(xlUp).row ' Last row of BillSch
        
        'This code works but I need the lastrow of DX to be a variable
        [T3:DX321].Formula = "=IFERROR(XLOOKUP(1,(BS_ORDERNO=$S3)*(BS_WEEKNO=T$1),BS_CHARGEDPRICE),"""")"

'       These two attempts were failures
'        [T3:DX" & lastrow].Formula = "=IFERROR...."  'ERROR--  "Runtime error 434 Object required

'        Dim Rng As Range
'        Rng = BillSch.Range("T3:DX" & Lastrow)  'ERROR--  "Object variable or With block variable not set"
'        Rng.Formula = "=IFERROR(XLOOKUP(1,(BS_ORDERNO=$S3)*(BS_WEEKNO=T$1),BS_CHARGEDPRICE),"""")"
End Sub

Solution

Reference a Range

You forgot to use the Set keyword as mentioned by Ron Rosenfeld in the comments:
```
Set rng = BillSch.Range("T3:DX" & LastRow)
```
BTW, you don't want to use [T3:DX321] ever since it means the risky ActiveSheet.Range("T3:DX321") opposed to the safe BillSch.Range("T3:DX321").
Here are just a few other ways how to tackle this.

Hard Coded (Last Row)

Dim rng As Range, LastRow As Long

With BillSch
    LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
    Set rng = .Range("T3", .Cells(LastRow, "DX"))
End With

Hard Coded

Dim rng As Range

With BillSch.Range("S3")
    Set rng = .Resize(BillSch.Cells(BillSch.Rows.Count, .Column) _
        .End(xlUp).Row - .Row + 1).EntireRow.Columns("T:DX")
End With

Dynamic (Constants)

Const FIRST_CELL As String = "S3"
Const RANGE_COLUMNS As String = "T:DX"

Dim rng As Range

With BillSch.Range(FIRST_CELL)
    Set rng = .Resize(BillSch.Cells(BillSch.Rows.Count, .Column) _
        .End(xlUp).Row - .Row + 1).EntireRow.Columns(RANGE_COLUMNS)
End With

The latter is the basis for creating a function...

Function GetRangeEnd( _
    ByVal ws As Worksheet, _
    ByVal LastRowFirstCellAddress As String, _
    ByVal RangeColumnsAddress As String) _
As Range

    If ws Is Nothing Then Exit Function
    
    Dim rg As Range, rCount As Long
    
    With ws.Range(LastRowFirstCellAddress)
        rCount = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        If rCount < 1 Then Exit Function ' no data in column range
        Set GetRangeEnd = .Resize(rCount).EntireRow.Columns(RangeColumnsAddress)
   End With

End Function

... which you could utilize with

Dim rng As Range: Set rng = GetRangeEnd(BillSch, "S3", "T:DX")