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

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
[email protected]HIS

"Johnny" <[email protected]> wrote in message
news:[email protected]...
> 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")
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]HIS
>
> "Johnny" <[email protected]> wrote in message
> news:[email protected]...
> > 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")
> >
>
>

Bob, Nick,

Huge help! Many thanks.

John