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

 

[davesweep]

Hello,

Selecting the cells containing the intended range name and then running this code should do what you require.

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

 

[andadd]

Thank you! That worked exactly how i wanted it too.

I do have a follow up question...

Is it possible to add to this macro, that i want the selected cells (now the named range) to create a dynamic chart?

The code i have now will define the selected cell as a dynamic range and save the name to the name manager, for example if i select sales and run the macro it will create a dynamic range using this code :

[COLOR=#333333]Sub rangename()[/COLOR]
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 
[COLOR=#333333]End Sub
[/COLOR]

Now i want to try to run a macro that will create a chart by selecting the different cells i want as my SERIES or ranges for my chart, So what do i have to add to the code above to be able to use the defined ranges and make a dynamic chart?

Regards

 

[davesweep]

This should give you a start

Sub add_and_populate_chart()
Dim rCell As Range, rSelectedRange As Range
Dim iSeries As Integer, iCount As Integer

iCount = 1
Set rSelectedRange = Selection
iSeries = rSelectedRange.Count


ActiveSheet.Shapes.AddChart.Select
ActiveChart.SeriesCollection(1).Delete
    
For Each rCell In rSelectedRange
    With ActiveChart
    .ChartType = xlLine
    .SeriesCollection.NewSeries
    .SeriesCollection(iCount).Name = rCell.Value
    .SeriesCollection(iCount).Values = "=" & ActiveWorkbook.Name & "!" & rCell.Value
    End With

    iCount = iCount + 1
    
Next rCell

rSelectedRange.Select

End Sub