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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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,887
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,887
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,520
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top