Automate defining names
 

VBA snippet:

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie", RefersToR1C1:="='11'!R2C2"

Two things I need to do:

1. Substitute week for '11' (having syntax problem).
2. AND week with Eddie (for example =" "Eddie" & week")

Automate defining names
 

Johnny

try this

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie_" & week, RefersToR1C1:="=" & week &
"!R2C2"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Johnny" wrote in message
oups.com...
VBA snippet:

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie", RefersToR1C1:="='11'!R2C2"

Two things I need to do:

1. Substitute week for '11' (having syntax problem).
2. AND week with Eddie (for example =" "Eddie" & week")

Automate defining names
 

Nick,

Works as requested, thank you. But here's a followup question if you
don't mind:

Instead of hard-coding the names (ie. Eddie), what would the syntax
change be to get the name from the adjacent cell (ex. B2). I've tried
substituting both B2 and R2C2, and I get a run-time error 1004.

Thanks again
John

Automate defining names
 

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:= Range("B2").Value & "_" & week,
RefersToR1C1:="=" & week & "!R2C2"

or if it is relative (one column to the right) to the activecell then

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:= Activecell.Offset(0,1).Value & "_" & week, _
RefersToR1C1:="=" & week & "!R2C2"

--
HTH

Bob Phillips

"Nick Hodge" wrote in message
...
Johnny

try this

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie_" & week, RefersToR1C1:="=" & week &
"!R2C2"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Johnny" wrote in message
oups.com...
VBA snippet:

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie", RefersToR1C1:="='11'!R2C2"

Two things I need to do:

1. Substitute week for '11' (having syntax problem).
2. AND week with Eddie (for example =" "Eddie" & week")

Automate defining names
 

Bob, Nick,

Huge help! Many thanks.

John