Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate defining names
Bob, Nick,
Huge help! Many thanks. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defining names in a macro | Excel Discussion (Misc queries) | |||
Help with defining names | Excel Worksheet Functions | |||
Defining Names in Excel | Excel Worksheet Functions | |||
Automating defining names | Excel Programming | |||
Defining Names | Excel Programming |