how to set a formula name by using cell values

[Guest]

First time set formula name.
Can I set a name to a formula's by refering to certain cell's value in other
worksheet?
And, can I use a variable (this case is "C") in the formula and define it
later when calling this named fomula?

ActiveWorkbook.Names.Add Name = Range("legend!A2").Value, RefersToR1C1 =
"=N(INDIRECT(RC[-15]&""_""&R[" & (1 - C) & "]C[-16]))"

Thanks for advise!

 

[Guest]

If set a name, it works.

ActiveWorkbook.Names.Add Name:="Att",
RefersTo:="=N(INDIRECT(RC[-15]&""_""&R[" & (1-C) & "]C[-16]))"

But the variable "C" still not working even assign it a value later.

Qty = 6
C = 1
Activecell.Select
With Selection
Do Until C > Qty
Selection.Value = "=Att"
.Offset(C).Select
C = C + 1
Loop
End With

Still need to assign the name as one cell's value and use the variable to
assign the same row value to different cell. How to get it done?