Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Fixed text code needs replacing with variable text
I would like to change the below code so that all of the current fixed text
typed "Sheet1" are replaced with a variable text and the variable text is equal to the ActiveSheet.Name I can see there are two parts too resolving this 1) the variable text will be only part of the define name and 2) the variable text in the formula entry within the define name Any help with this would be fantastic Many thanks Steve ActiveWorkbook.Names.Add Name:="Oracle_Travel_Sheet1", RefersToR1C1:= _ "=OFFSET(Sheet1!R12C2,0,0,COUNTA(Sheet1!C2),COUNTA (Sheet1!R12))" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Fixed text code needs replacing with variable text
Try this (untested):
myName = ActiveSheet.Name ActiveWorkbook.Names.Add Name:="Oracle_Travel_" & myName, RefersToR1C1:= _ "=OFFSET(myName & !R12C2,0,0,COUNTA(myName & !C2),COUNTA(myName & !R12))" HTH knut "steven.holloway" skrev i melding ... I would like to change the below code so that all of the current fixed text typed "Sheet1" are replaced with a variable text and the variable text is equal to the ActiveSheet.Name I can see there are two parts too resolving this 1) the variable text will be only part of the define name and 2) the variable text in the formula entry within the define name Any help with this would be fantastic Many thanks Steve ActiveWorkbook.Names.Add Name:="Oracle_Travel_Sheet1", RefersToR1C1:= _ "=OFFSET(Sheet1!R12C2,0,0,COUNTA(Sheet1!C2),COUNTA (Sheet1!R12))" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Fixed text code needs replacing with variable text
Dim ShtName as string
shtname = "Sheet1" ActiveWorkbook.Names.Add Name:="Oracle_Travel_" & shtname, _ RefersToR1C1:="=OFFSET('" & shtname & "'!R12C2,0,0," _ & "COUNTA('" & shtname & "'!C2),COUNTA('" & shtname & "'!R12))" But there are some characters that can't be used in the name:= portion. You'll want to be careful. The apostrophes inside the referstor1c1:= portion will let any valid name work there. steven.holloway wrote: I would like to change the below code so that all of the current fixed text typed "Sheet1" are replaced with a variable text and the variable text is equal to the ActiveSheet.Name I can see there are two parts too resolving this 1) the variable text will be only part of the define name and 2) the variable text in the formula entry within the define name Any help with this would be fantastic Many thanks Steve ActiveWorkbook.Names.Add Name:="Oracle_Travel_Sheet1", RefersToR1C1:= _ "=OFFSET(Sheet1!R12C2,0,0,COUNTA(Sheet1!C2),COUNTA (Sheet1!R12))" -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - Fixed text code needs replacing with variable text
Thanks DS & Dave, exactly what I needed.
Many thanks again Steve "Dave Peterson" wrote: Dim ShtName as string shtname = "Sheet1" ActiveWorkbook.Names.Add Name:="Oracle_Travel_" & shtname, _ RefersToR1C1:="=OFFSET('" & shtname & "'!R12C2,0,0," _ & "COUNTA('" & shtname & "'!C2),COUNTA('" & shtname & "'!R12))" But there are some characters that can't be used in the name:= portion. You'll want to be careful. The apostrophes inside the referstor1c1:= portion will let any valid name work there. steven.holloway wrote: I would like to change the below code so that all of the current fixed text typed "Sheet1" are replaced with a variable text and the variable text is equal to the ActiveSheet.Name I can see there are two parts too resolving this 1) the variable text will be only part of the define name and 2) the variable text in the formula entry within the define name Any help with this would be fantastic Many thanks Steve ActiveWorkbook.Names.Add Name:="Oracle_Travel_Sheet1", RefersToR1C1:= _ "=OFFSET(Sheet1!R12C2,0,0,COUNTA(Sheet1!C2),COUNTA (Sheet1!R12))" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Variable Text in Text Boxes...... | Excel Discussion (Misc queries) | |||
replacing text in all cells with existing text plus something | Excel Discussion (Misc queries) | |||
Replacing #N/A with other text | Excel Worksheet Functions | |||
Help replacing text with Yes or No | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |