Defining a dynamic named range with the index formula in VBA

[andadd]

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

 

[andadd]

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]

Hi
Welcome to the board

Try:

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

 

[andadd]

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:

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]

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

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

 

[pgc01]

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]

I have this code now and it worked:

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

 

[pgc01]

I'm glad you got it working. Cheers!