Help setting variable range name

[esskaykay]

Hello,

I am attempting to create a VBA routine that defines a data range (LookupDate). The first cell in the range remains constant (P33); however, the last row changes. I have a cell (S17) that displays the last row where the range should end. I believe I am looking for a way to change the row marker (R87) in the following line of code to be based on the value in cell S17

ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R87C16"

Something like
ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:RS17C16"
Or
ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R�S17�C16"
Or
ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R&�S17�C16"
Or
ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R" & S17 & "C16"
Or
?????


Here is my current (erroneous) macro:

Please Login or Register  to view this content.

Any suggestions would be appreciated. Thanks

Thanks for viewing,
Steve K.

[esskaykay]

I think I figured out my answer (I am slowly getting better at this). I needed to add the Range parameter. It appears to be working. . .

ActiveWorkbook.Names.Add Name:="LookupDate", RefersToR1C1:="=Amortize!R33C16:R" & Range("S17") & "C16"

[bebo021999]

Alternative way:

Sub LookupRange()
    Dim LookupDate As Range
    Dim LastRow As Long
    
    ' Get the last row number from cell S17
    LastRow = Range("S17").Value
    
    ' Define the range using the constant first cell (P33) and the dynamically determined last row
    Set LookupDate = Sheets("Amortize").Range("P33:R" & LastRow)
    
    ' Add the defined range as a named range
    ThisWorkbook.Names.Add Name:="LookupDate", RefersTo:=LookupDate
    
End Sub 

[esskaykay]

Thank you bebo - much appreciated.