Adding Dynamic Named Range using VBA fails

[BrentlyBoy]

First a bit of background. I use a number of conditional formats on the vast majority of spreadsheets I use. Some of them dynamically format the title row in a spreadsheet, others shade alternate rows (using the subtotal method to account for filtering). There are others, but I won't bore you with the details. It's a pain to have to go in and manually set these up all the time. I tend to get around a bit of the manual setup by having a standard template that has some of this already set up but that isn't helpful when I get spreadsheets that have been started by other people (or people muck with the named ranges). The formula's for some of these are quite complex and due to the way Excel deals with Conditional Formatting (when users delete rows, columns, add columns and rows, etc, Conditional Formatting gets stuffed up fairly quickly. As a result, I have leaned towards using Named Ranges for the more complex formula's - making it easier for users to utilize and for me to correct conditional formats. I have code that goes through and refreshes the standard conditional formatting to try and deal with users changes to the worksheets.

Okay, so that brings me to what I am trying to do.

I have a number of dynamic named ranges that I use that are non-worksheet specific. In other words, they work on the active worksheet or the worksheet they are being used in (see Ricyteach post here for a better explanation. This is achieved by using the "!" prior to the cell reference (and no sheet name). Now the named range will refer to that cell reference on the active worksheet. An example of one of these named ranges is this one;

=AND(MOD(SUBTOTAL(3,!$A$1:$A2),2)=0,ROW()>1,NOT(ISBLANK(!$A2)),NOT(ISBLANK(!A$1)))

This formula works great as a named ranged ( that I call FUNC_ShadeLine_Even) when I enter it via the Name Manager.

So this is where I need your help. I store the Named Range name and the RefersTo formula in a worksheet. When I use VBA to add the Named Range with formula's that use a fully qualified worksheet name (eg; 'Deliverables'!A3) it works fine. But if I attempt to create a Named Range that uses a non-qualified cell reference (eg, !A3) it fires an error 1004 - There's an error in the formula you entered. The name works fine if entered via the name manager, but won't allow me to add it via VBA.

The code I'm using to add it to VBA is below;

    Dim rCell as Range
    For Each rCell In ActiveWorkbook.Worksheets("Name's").Range("tmp_Names").Cells
        ActiveWorkbook.Names.Add Name:=rCell.Text, RefersTo:=rCell.Offset(0, 1).Formula
    Next rCell

I'd really appreciate any help to resolve this.

Cheers.

 

[Akuini]

Hi, BrentlyBoys. Welcome to the Forum.
It looks like you need to convert the formula to R1C1 style.
I tried this & it worked:

Sub a1160476a()
tx = "=!$A$1"
tx = Application.ConvertFormula(Formula:=tx, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
ActiveWorkbook.Names.Add Name:="bbc", RefersToR1C1:=tx
End Sub

I didn't try yours but maybe:

    Dim rCell As Range, tx
    For Each rCell In ActiveWorkbook.Worksheets("Name's").Range("tmp_Names").Cells
        tx = Application.ConvertFormula(Formula:=rCell.Offset(0, 1).Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
        ActiveWorkbook.Names.Add Name:=rCell.Text, RefersTo:=tx
    Next rCell

 

[Akuini]

Sorry, in your case I think it should be:

    For Each rCell In ActiveWorkbook.Worksheets("Name's").Range("tmp_Names").Cells
        ActiveWorkbook.Names.Add Name:=rCell.Text, RefersToR1C1:=rCell.Offset(0, 1).FormulaR1C1
    Next rCell