Excel vba add named range

Question

I trying to add named range with VBA

Nname = "FindMe"
Formulas = "Vlookup(" & Chr(34) & Nname & Chr(34) & ";Translations!$A:$C;2;False)"
ActiveWorkbook.Names.Add Name:=Nname, RefersToR1C1:=Formulas

But i get

Vlookup(""FindMe"";Translations!$A:$C;2;False)

Instead of

Vlookup("FindMe";Translations!$A:$C;2;False)

Why Excel put double qoutes? How to Get string which i want?

Vlookup("FindMe";Translations!$A:$C;2;False)

as it looks right now to me, you can only set a range as it is as RefersTo**, formulas always pop up an error (at least for me) — Dirk Reichel
– Dirk Reichel, Commented Nov 23, 2015 at 21:54

Bobby Orndorff · Accepted Answer · 2015-11-23 22:27:14Z

2

The string assigned to Formulas is missing an '=' at the beginning of the string. Thus, the name is being assigned a string constant instead of a formula. When you look in Name Manager dialog, you will that name FindMe refers to ="Vlookup(""FindMe"";Translations!$A:$C;2;False)". Note that two double quotes represents a single quote in a string constant.

If you add a '=' to the beginning of the string, then name will be assigned a formula. Also, the formula is using A1 notation but the VBA code is using RefersToR1C1. This will cause a formula parsing error. Either change the formula to R1C1 notation or change the VBA code to use RefersTo. For example...

Nname = "FindMe"
Formulas = "=Vlookup(" & Chr(34) & Nname & Chr(34) & ",Translations!$A:$C,2,False)"
ActiveWorkbook.Names.Add Name:=Nname, RefersTo:=Formulas

answered Nov 23, 2015 at 22:27

Bobby Orndorff

3,3551 gold badge11 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Excel vba add named range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related