Excel Names. VBA. RefersToR1C1 versus RefersTo. Where is the catch?

[DEksel]

I am adding (or overwriting the existing) name using VBA. Var str is String.
This one works:

    str = "=IFERROR(INDEX(Tickets!R5C2:R1024C13,MATCH(!RC4,Tickets!R5C4:R1024C4,0),!R3C2),0)"
    ActiveWorkbook.Names.Add Name:="DGrab", RefersToR1C1:=str
    ActiveWorkbook.Names("DGrab").Comment = "Nice one!"

The next one not.

    str = "=IFERROR(INDEX(Tickets!$B$5:$M$2048,MATCH(!$D5,Tickets!$D$5:$D$2048,0),!$B$3),0)"
    ActiveWorkbook.Names.Add Name:="DGrab", RefersTo:=str
    ActiveWorkbook.Names("DGrab").Comment = "Nice one!"

Why? I always hated R1C1 way...

 

[RoryA]

Looks like a bug to me.

 

[DEksel]

Looks like a bug to me.

Thanks m8! Nothing else I could find. I need to refresh that name all the time because each time when I kill some rows (and need to do it often or change their places), lookup range is changed too even it is fixed...

 

[RoryA]

If you delete rows, the ranges will always adjust accordingly. To avoid that you could use INDIRECT or perhaps INDEX instead.

 

[DEksel]

If you delete rows, the ranges will always adjust accordingly. To avoid that you could use INDIRECT or perhaps INDEX instead.

And be more concrete? Can you write the 'str' which will not change when I kill or move the ranges?

 

[RoryA]

You could use something like:

str = "=IFERROR(INDEX(INDIRECT(""Tickets!$B$5:$M$2048""),MATCH(INDIRECT(""RC4"",0),INDIRECT(""Tickets!$D$5:$D$2048""),0),INDIRECT(""R3C2"",0)),0)"

 

[DEksel]

You could use something like:

str = "=IFERROR(INDEX(INDIRECT(""Tickets!$B$5:$M$2048""),MATCH(INDIRECT(""RC4"",0),INDIRECT(""Tickets!$D$5:$D$2048""),0),INDIRECT(""R3C2"",0)),0)"

Sorry but that one simple does not work. Added without problems but "The Boy Does Nothing".

I have small procedure which I am running each time after killing or moving the rows... so I've added 'Create name' lines there and all is ok. I was just currious... If you really think that indirect will help (but have to work of course!), let's continue...

Thank you very much.

 

[RoryA]

It works for me. How are you using it?

 

[DEksel]

It works for me. How are you using it?

Eeee... That's the right question!!!To do or not to do!

 

[DEksel]

Eeee... That's the right question!!!To do or not to do!

My Workbook is too advanced... I can post it somewhere and post here the link... But I must explain than what and when... Do you want me to do so?