[SOLVED] Names.Add in VBA using variables

Names.Add in VBA using variables

How can I create the following non-contiguous named range using the following variables?

ActiveWorkbook.Names.Add Name:="Validation_Range", RefersToR1C1:= _
        "='Holding Template'!R3C1,'Holding Template'!R4C2"

Here are the variables:

Dim PM As Range
Dim statement_date As Range

Set PM = ws.Range("A3")
Set statement_date = ws.Range("B4")

If I use 1 variable it works but I am unable to use more than one variable to create the non-contiguous range.

Solution

It is not clear how you are getting the two ranges to set your variables to but here are some shorthand options using the Union method.

Dim pm As Range, statement_date As Range
With Worksheets("Holding Template")
    Union(.Cells(3, 1), .Cells(4, 2)).Name = "Validation_Range"
    'alternate
    'Union(.Range("A3"), .Range("B4")).Name = "Validation_Range"
    'alternate
    'Set pm = .Range("A3")
    'Set statement_date = .Range("B4")
    'Union(pm, statement_date).Name = "Validation_Range"
End With

Any of these methods creates a named range of workbook scope or overwrites the RefersTo: of an existing Validation_Range named range with workbook scope.