Creating a mixed reference named range with VBA-CodePudding

I'm trying to write some VBA that can create a named range if it matches a header. I've managed to achieve this but hit a snag that I can't seem to get to work.

I need the named range to be mixed reference only locking in the column. I've got this to work also but when trying to combine everything it doesn't carry the mixed ref.

Examples -

    Sub test()

i = 1
Do Until Cells(1, i) = "Created Date"
i = i   1
Loop

NR1 = Cells(1, i).Offset(1, 0).Address(False, True)


ActiveWorkbook.Names.Add Name:="Created_Date", RefersTo:=NR1
ActiveWorkbook.Names("Created_Date").Comment = ""


End Sub

The above will set the named range with the required mixed ref but obviously no sheet name -

so my thought was simply use activesheet.range(NR1) like so -

Sub test()

i = 1
Do Until Cells(1, i) = "Created Date"
i = i   1
Loop

NR1 = Cells(1, i).Offset(1, 0).Address(False, True)

ActiveWorkbook.Names.Add Name:="Created_Date", RefersTo:=ActiveSheet.Range(NR1)
ActiveWorkbook.Names("Created_Date").Comment = ""

End Sub

This does create the named range on the sheet but the references go back to being locked to one cell!

any ideas?

CodePudding user response：

You can use the External argument in the Range.Address property to make the Workbook and Sheet name part of the returned address.

If you put an address into the RefersTo argument, it just takes the string as its value instead of going to the cells or range that the address is pointing at. To fix this interaction, you can add an = in front of the address to make the String into a formula which Excel will evaluate.

So with the following changes, your code should do what you want:

NR1 = Cells(1, i).Offset(1, 0).Address(False, True, External:=True)


ActiveWorkbook.Names.Add Name:="Created_Date", RefersTo:="=" & NR1