Set up dynamic named range using vba

Dave Edmonds · Jun 17th 2016

HI, this sub works but puts "" around the range in name manager. Any ideas on how to overcome this?

Code

Option Explicit
 
Sub NamedRange()
     
    Dim Rng As Range
    Dim x
    Dim Range_name
    Dim Range_String
    
    ActiveSheet.Range("B3:B8").Select
     
    For Each x In Selection
    
    Range_name = x
    Debug.Print Range_name
   Set Rng = x.Offset(0, 1)
   
    ActiveWorkbook.Names.Add Name:=Range_name, RefersTo:=Rng.Value
    
    
    Next x
     
End Sub

Display More

Dave

skywriter · Jun 17th 2016

Re: Set up dynamic named range using vba

Names don't always refer to cells, sometimes they hold a value.

This code loops through B3:B8 and creates six new names in the name manager, the names are what's in those cells and what they refer to is not a range but the values in the cells in the C column on the same row.

If after you run the code you delete the values in the B column and the C column it has no effect on the names or what they contain.

Let's say B3 has the text "Test" in it and C3 has the value "Run" in it, then after running the code we could type =Test into a blank cell and the result would be Run in the cell after we hit enter.

Names can even be used to create formulas.

What is it you want to do?

Dave Edmonds · Jun 18th 2016

Re: Set up dynamic named range using vba

Hi thanks for the reply. Just to clarify, I understand the code, I wrote it.
i have 400 named ranges to set up in a workbook and this is to automate the task to save hours of manual work. B has a named range and C has the text of a formula that creates a dynamic named range. The macro copies the name and the formula to the name manager. Unfortunately when it copies the formula it puts " " around it and the name manager doesn't recognise it. I was trying to see how to copy it without " ". Thanks for trying to help though.
Dave

skywriter · Jun 18th 2016

Re: Set up dynamic named range using vba

Try rng.Formula and if that doesn't work post an example.

Dave Edmonds · Jun 20th 2016

Re: Set up dynamic named range using vba

Genius!! Thanks pal.
i had tried rng.value and rng.text etc and knew it would be a different operator but couldn't work it out.
Many thanks
Dave

skywriter · Jun 21st 2016

Re: Set up dynamic named range using vba

My pleasure, thanks for the feedback. :cool:

Set up dynamic named range using vba

Participate now!

Share