Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a Range with VB
I have a loop that I run through where the name of the
CurrentRange variable changes each time through. For example, if the CurrentRange variable is "Justin's Range" and the CurrentSheet variable is "Justin's Sheet", I want to name the range, "Justin's Range", with the following formula: "=OFFSET("Justin's Sheet"!$A$1,0,0, COUNTA("Justin's Sheet"!$A$1:$A$365),1)" This is the code I'm using: ActiveWorkbook.Names.Add Name:=CurrentRange, RefersToR1C1:= _ "=OFFSET(CurrentSheet!$A$1,0,0, COUNTA(CurrentSheet!$A$1:$A$365),1)" I think it's trying to find a sheet called "CurrentSheet" instead of computing the formula out. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a Range with VB
ActiveWorkbook.Names.Add Name:=CurrentRange,
RefersToR1C1:= _ "=OFFSET('" & Activesheet.Name & "'!$A$1,0,0, COUNTA('" & ActiveSheet.Name & "'!$A$1:$A$365),1)" Note the addition of single quotes on each side of ActiveSheet.Name since the sheet name could contain spaces. -- Regards, Tom Ogilvy "Justin" wrote in message ... I have a loop that I run through where the name of the CurrentRange variable changes each time through. For example, if the CurrentRange variable is "Justin's Range" and the CurrentSheet variable is "Justin's Sheet", I want to name the range, "Justin's Range", with the following formula: "=OFFSET("Justin's Sheet"!$A$1,0,0, COUNTA("Justin's Sheet"!$A$1:$A$365),1)" This is the code I'm using: ActiveWorkbook.Names.Add Name:=CurrentRange, RefersToR1C1:= _ "=OFFSET(CurrentSheet!$A$1,0,0, COUNTA(CurrentSheet!$A$1:$A$365),1)" I think it's trying to find a sheet called "CurrentSheet" instead of computing the formula out. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming a Range with VB
Thanks for your response.
-----Original Message----- ActiveWorkbook.Names.Add Name:=CurrentRange, RefersToR1C1:= _ "=OFFSET('" & Activesheet.Name & "'!$A$1,0,0, COUNTA('" & ActiveSheet.Name & "'!$A$1:$A$365),1)" Note the addition of single quotes on each side of ActiveSheet.Name since the sheet name could contain spaces. -- Regards, Tom Ogilvy "Justin" wrote in message ... I have a loop that I run through where the name of the CurrentRange variable changes each time through. For example, if the CurrentRange variable is "Justin's Range" and the CurrentSheet variable is "Justin's Sheet", I want to name the range, "Justin's Range", with the following formula: "=OFFSET("Justin's Sheet"!$A$1,0,0, COUNTA("Justin's Sheet"!$A$1:$A$365),1)" This is the code I'm using: ActiveWorkbook.Names.Add Name:=CurrentRange, RefersToR1C1:= _ "=OFFSET(CurrentSheet!$A$1,0,0, COUNTA(CurrentSheet!$A$1:$A$365),1)" I think it's trying to find a sheet called "CurrentSheet" instead of computing the formula out. Any help would be appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming a Data Range | Excel Discussion (Misc queries) | |||
Naming a range | Excel Discussion (Misc queries) | |||
Range naming | Excel Discussion (Misc queries) | |||
Naming a range | Excel Discussion (Misc queries) | |||
Range naming | Excel Programming |