VBA error adding a Name with a formula in the reference

Question

I am trying to add a Name to my workbook. The reference has an INDEX formula. I am getting an error on this line of code:

ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=Reference

I tried it with ActiveWorkbook and also tried defining a worksheet.

I guess it doesn't work, because the name range can not be matched with a worksheet because it has a function in it, but i do not know how to solve. Does anyone have a suggestion?

Sub NameRange_Add3()
Dim RangeName As String
Dim Reference As String
Dim i As Integer


For i = 2 To 6
    RangeName = "list" & i
    Reference = "=INDEX(tabla_1;;MATCH(" & "hszis" & i & ";hszi_list;0))"
    ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=Reference
Next i

End Sub

arcadeprecinct · Accepted Answer · 2016-11-04 08:05:56Z

3

When you are using creating formulas in VBA you need to use the English notation, which means points as decimal separators and commas as function argument separators.

You can either do what @brettdj did and use commas

Reference = "=INDEX(tabla_1,,MATCH(" & "hszis" & 1 & ",hszi_list,0))"

or use RefersToLocal instead of RefersTo

ActiveWorkbook.Names.Add Name:=RangeName, RefersToLocal:=Reference

I would prefer the first solution though because otherwise it could fail if you execute the macro on a machine with different language settings.

answered Nov 4, 2016 at 8:05

arcadeprecinct

3,7771 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2016-11-04 07:47:07Z

2

I ran it with

Reference = "=INDEX(tabla_1,MATCH(" & "hszis" & i & ",hszi_list,0))"

and it worked. Suggest you try removing the bonus ;

Reference = "=INDEX(tabla_1;MATCH(" & "hszis" & i & ";hszi_list;0))"

edited Nov 4, 2016 at 7:47

answered Nov 4, 2016 at 7:45

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

2 Comments

Robin Mackenzie Over a year ago

do you mean the bonus ; ?

Tatár Tamás Over a year ago

ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=Reference

Collectives™ on Stack Overflow

VBA error adding a Name with a formula in the reference

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related