Scripting Creation of Defined Names referring to a formula

I am currently using a formula inside of a named range to create a drop list and remove any gaps. I have a project where I need to create several hundred of these named ranges for use in drop down lists and I want to avoid having to do all of this manually in name manager. Is there a way that I can stage the attributes for the named range inside a group of cells and reference those inside of some VB to create them?

I was thinking of staging the names of the named ranges inside of a range of cells and the formula it would refer to inside of a different range of cells.

Here is an example of the formula I want to have the named range refer to: =Sheet1!$G$57:INDEX(Sheet1!$G$57:$G$76,COUNTIF(Sheet1!$G$57:$G$76,"*?")+COUNTIF(Sheet1!$G$57:$G$76,">1"))

I am getting closer so maybe my progress will spawn some inspiration for someone better at this to help me finish it. I used the macro recorder to get this formula for creating the names.

ActiveWorkbook.Names.Add Name:="NewNamedReference", RefersToR1C1:= _
"=Sheet1!R1C1:R13C1"

What I would like to do is have the "NewNamedReference" value be the value from a specific cell and the "=Sheet1!R1C1:R13C1" value be the FORMULA inside of the cell next to it. The final component would be having it loop this command on the next row until it ran out of rows containing data. Hopefully this description is more helpful.

getting better with this one.

ActiveWorkbook.Names.Add Name:=Range("A28"), RefersToR1C1:=Range("B28").FormulaR1C1

How do I increment A28 and B28 by one until I run out of entries in column A? Setting a predetermined range would be acceptable as well.

So here is what ended up working for me. It was a little klunky but still saved me some time.


Dim X As Integer, Row As Integer
Row = 28 'here I set the first row that contained my staged data.
For X = 28 To 207 'here is the range for the total number of rows that needed to be converted into names


ActiveWorkbook.Names.Add Name:=Range("A" & Row), RefersToR1C1:=Range("B" & Row).FormulaR1C1
'The "A"&Row cell contained the name used to create the defined name and the "B"&Row are the cells that contained the formula inside of the cell.

Row = Row + 1

Next X

There may have been a more elegant method of writing this but it got the job done. Hope it helps someone else.