Redefining a named range in Excel VBA

[bisel]

Hello All,

I have a workbook with some named ranges. Depending on cell's value, I want to change the range values of the named range. Here is the code that I have tried without success ...

:
Select Case Sheet23.Range("allocation_type").Value
    Case Is = "Do Not Include"
            ActiveWorkbook.Names("allocation_unit_table").RefersTo = Sheet23.Range("AG43")                   ' Set range to single blank cell 
            ActiveWorkbook.Names("allocation_summary_table").RefersTo = Sheet23.Range("AG44")           ' Set range to single blank cell 
    Case Is = "Uniform Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersTo = Sheet23.Range("AG43")                    ' Set range to single blank cell 
            ActiveWorkbook.Names("allocation_summary_table").RefersTo = Sheet23.Range("AG44")            ' Set range to single blank cell 
    Case Is = "Variable Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersTo = Sheet23.Range("AE7:AK22")              ' Set range to the unit table for variable rate 
            ActiveWorkbook.Names("allocation_summary_table").Refers = Sheet23.Range("B50:O83")           ' Set range to the summary table for variable rate 
    Case Is = "Hybrid/Blended Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersTo = Sheet23.Range("AE25:AK40")            ' Set range to the unit table for hybrid rate 
            ActiveWorkbook.Names("allocation_summary_table").Refers = Sheet23.Range("B91:Q124")         ' Set range to the summary table for hybrid rate
    Case Else
            ActiveWorkbook.Names("allocation_unit_table").RefersTo = Sheet23.Range("AG43")                     ' Set range to single blank cell 
            ActiveWorkbook.Names("allocation_summary_table").RefersTo = Sheet23.Range("AG44")             ' Set range to single blank cell 
End Select
:

I have tried variations of the above without success. I notice that with the "ActiveWorkbook.Names ... " there are several variations such as .Names.Item. And with .RefersTo there are variations such as .RefersToRange. I have tried several things but nothing is working as I would like. Any help is appreciated.

Regards,

Steve

 

[Cubist]

Something like this...maybe.

Dim allocationType As String
allocationType = Sheet23.Range("allocation_type").Value

Dim unitTableRange As Range
Dim summaryTableRange As Range

Select Case allocationType
    Case "Variable Rate"
        Set unitTableRange = Sheet23.Range("AE7:AK22")
        Set summaryTableRange = Sheet23.Range("B50:O83")
    Case "Hybrid/Blended Rate"
        Set unitTableRange = Sheet23.Range("AE25:AK40")
        Set summaryTableRange = Sheet23.Range("B91:Q124")
    Case "Do Not Include", "Uniform Rate", Else
        Set unitTableRange = Sheet23.Range("AG43")
        Set summaryTableRange = Sheet23.Range("AG44")
End Select

ActiveWorkbook.Names("allocation_unit_table").RefersTo = unitTableRange
ActiveWorkbook.Names("allocation_summary_table").RefersTo = summaryTableRange

 

[bisel]

Something like this...maybe.

Dim allocationType As String
allocationType = Sheet23.Range("allocation_type").Value

Dim unitTableRange As Range
Dim summaryTableRange As Range

Select Case allocationType
    Case "Variable Rate"
        Set unitTableRange = Sheet23.Range("AE7:AK22")
        Set summaryTableRange = Sheet23.Range("B50:O83")
    Case "Hybrid/Blended Rate"
        Set unitTableRange = Sheet23.Range("AE25:AK40")
        Set summaryTableRange = Sheet23.Range("B91:Q124")
    Case "Do Not Include", "Uniform Rate", Else
        Set unitTableRange = Sheet23.Range("AG43")
        Set summaryTableRange = Sheet23.Range("AG44")
End Select

ActiveWorkbook.Names("allocation_unit_table").RefersTo = unitTableRange
ActiveWorkbook.Names("allocation_summary_table").RefersTo = summaryTableRange

Thank you very much. Your solution did the trick. And so simple too!

Best regards,

Steve