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))" |
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))" |
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 |
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 |
All times are GMT +1. The time now is 12:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com