Run a Macro that will define a dynamic range (i have been using the index formula) and name the range as the selected cell

[andadd]

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

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

 

[VoG]

Duplicate http://www.mrexcel.com/forum/excel-...g-index-formula-name-range-selected-cell.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).