Adding Dynamic Named Range using VBA fails

BrentlyBoy

New Member
Joined
Feb 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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;

Excel Formula:
=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;

VBA Code:
    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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Hi, BrentlyBoys. Welcome to the Forum.
It looks like you need to convert the formula to R1C1 style.
I tried this & it worked:
VBA Code:
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:
VBA Code:
    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

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
Sorry, in your case I think it should be:
VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,382
Messages
5,624,367
Members
416,025
Latest member
VN5968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top