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
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
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