Redefining a named range in Excel using VBA

[bisel]

Hello All,

I tried searching and cannot find an answer ... hoping someone can help.

I have a macro where I define named ranges. The VBA code works fine ...

:
Select Case Range("allocation_type").Value
    Case Is = "Do Not Include"
            ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R46C47"              ' Set range to single blank cell to suppress unit table in report
            ActiveWorkbook.Names("allocation_summary_table").RefersToR1C1 = "='Assessment Allocation'!R47C47"           ' Set range to single blank cell to suppress summary table in report
    Case Is = "Uniform Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R46C47"              ' Set range to single blank cell to suppress unit table in report
            ActiveWorkbook.Names("allocation_summary_table").RefersToR1C1 = "='Assessment Allocation'!R10C34:R44C35"    ' Set range to the summary table for uniform rate to print in the report
    Case Is = "Variable Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R50C32:R65C38"       ' Set range to the unit table for variable rate to print in the report
            ActiveWorkbook.Names("allocation_summary_table").RefersToR1C1 = "='Assessment Allocation'!R10C34:R44C36"    ' Set range to the summary table for variable rate to print in the report
    Case Is = "Hybrid/Blended Rate"
            ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R71C32:R87C38"       ' Set range to the unit table for hybrid rate to print in the report
            ActiveWorkbook.Names("allocation_summary_table").RefersToR1C1 = "='Assessment Allocation'!R10C39:R44C43"    ' Set range to the summary table to print in the report
    Case Else
            ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R46C47"              ' Set range to single blank cell to suppress space in report
            ActiveWorkbook.Names("allocation_summary_table").RefersToR1C1 = "='Assessment Allocation'!R47C47"           ' Set range to single blank cell to suppress space in report
End Select
:

What I want to do is to define the named ranges using a variable. I will define the row number and column number using a variable. For example ...

instead of ...

:
ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R50C32:R65C38" 
:

I want to do something like this ...

:
ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R50C32:rowandcolumnnumber" 
:

where "rowandcolumnnumber" is the row and column number that I would define depending on user input that would change the value of a target cell.

Hope someone can assist me.

Regards,

Steve

 

[Joe4]

Literal text goes inside quotes, variables go outside, and sewn together with &.

So if you had variables named rownum and column, your code should look like this:

ActiveWorkbook.Names("allocation_unit_table").RefersToR1C1 = "='Assessment Allocation'!R50C32:R" & rownum & "C" & colnum