Defining a dynamic named range with the index formula in VBA

andadd

New Member
Joined
Sep 16, 2014
Messages
25
Hi, i have multiple series that i want to define as a named ranges using the index formula

say row 1 is called payments
and row 2 is called phonecalls

I will be adding new data every month and i want my chart to update for each month. Earlier i have used the index formula that looks like this:
$B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)) then i would define and save it to the defined named range. This is fine but when i have 50 ranges to define it takes too long and i also have to do it in several sheets and define in all my charts.

I have gotten close to defining one range using VBA i think...

The code i have used is :

Sub rangename()
Dim Rng1 As range
Set Rng1 = ActiveCell.Offset(0, 1)
With ActiveCell.Add.Formula = "=($B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3)))"
ActiveWorkbook.Names.Add Name:=Selection, RefersTo:=Rng1
End With
End Sub

For now i am trying to run the macro from a selected cell and define the range and save the range with the name of the selected cell. It gets the name right but wont save the dynamic range. It just saves the cell next to the selected cell as the range.

I am also using Norwegian Excel, but it is index and match that is used.

If you could help me to do one at a time to begin with that will sure help .

Regards
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

andadd

New Member
Joined
Sep 16, 2014
Messages
25
Code:
Sub rangename()
Dim Rng1            As range
    Set Rng1 = ActiveCell.Offset(0, 1)
    With ActiveCell.FormulaArray = "=$B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3))"
    ActiveWorkbook.Names.Add Name:=Selection, RefersTo:=Rng1
    End With
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,850
Hi
Welcome to the board

Try:

Code:
ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:="=($B$3:INDEX($B$3:$S$3,MATCH(9.99E+307,$B$3:$Q$3)))"
 

andadd

New Member
Joined
Sep 16, 2014
Messages
25
Hi,

Thanks, that worked :)

I have a follow up, i cant find this information anywhere... When my selected cell has two words in it, Say a name.... Steve Frank The macro wont save the named range because it has spaces. Is it anyway that i can code the macro to take the name from that cell and delete the spaces to save the named range as SteveFrank??

My code looks like this so far:
Code:
Sub rangename()Dim Rng1            As range
    Set Rng1 = ActiveCell.Offset(0, 1)
    With ActiveCell.FormulaArray = "=$B$3:INDEKS($B$3:$S$3;SAMMENLIGNE(9,99E+307;$B$3:$Q$3))"
    ActiveSheet.Names.Add Name:=ActiveCell.Value, RefersTo:="=$B$3:INDEX($B$3:$S$3,MATCH(9.99E+307,$B$3:$Q$3))"
    End With
End Sub

Regards
 

andadd

New Member
Joined
Sep 16, 2014
Messages
25

ADVERTISEMENT

Edited Question

Hi, i posted a question earlier about how to name a range and then define the range as a formula making the range dynamic: the formula looked like this: $X$10:INDEX($X$10:$AO$10,MATCH(9.99E+307,$X$10:$AM$10))


The name of the range is the cell to the left.

So now i have a code for that specific range

Code:
Sub rangename()
Dim Rng1            As Range    
Set Rng1 = ActiveCell.Offset(0, 1)    
With ActiveCell.FormulaArray = "=($X$10:INDEX($X$10:$AO$10,MATCH(9.99E+307,$X$10:$AM$10)))"    
ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:="=($X$10:INDEX($X$10:$AO$10,MATCH(9.99E+307,$X$10:$AM$10)))"    
End With    
End Sub

How can i code this so all the cells i select will generate a dynamic range equivalent too the index formula above,but for the row that the selected cell is on. Also, how can i get the name to replace spaces with underscores? i cant run this macro if my Serie has a name like "payments done"i need it to be able to either save the name as paymentsdone or payments_done.

Hope you can help :)

Regards Andreas
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,850
Hi Andreas

Not sure I understood correctly.

Ex.

Let's say cell A15 in worksheet "Sheet1" is the active cell

Sheet1!A15 has the value: "payments done"

Now you want to create the dynamic named range

Name: "payments_done"

Refers To: "=(Sheet1!$X$15:INDEX(Sheet1!$X$15:$AO$15,MATCH(9.99E+307,Sheet1!$X$15:$AM$15)))"

Did I understand correctly?
 

andadd

New Member
Joined
Sep 16, 2014
Messages
25
I have this code now and it worked:


Code:
Sub rangename()
Dim rCell As Range
Dim iRw As Integer

For Each rCell In Selection 
   iRw = rCell.Row
   ActiveWorkbook.Names.Add Name:=Replace(rCell.Value, " ", "_"), RefersTo:="=($X$" & iRw & ":INDEX($X$" & iRw & ":$AO$" & iRw & ",MATCH(9.99E+307,$X$" & iRw & ":$AM$" & iRw & ")))"Next rCell
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,385
Messages
5,528,396
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top