CODEWITHSUNDEEP
vbaexcelworksheet-function
Tatár Tamás
Tatár Tamás

Reputation: 73

VBA error adding a Name with a formula in the reference

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

Upvotes: 2

Views: 1407

Answers (2)

arcadeprecinct
arcadeprecinct

Reputation: 3777

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.

Upvotes: 3

brettdj
brettdj

Reputation: 55692

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

Upvotes: 2

Related Questions