JH987
08-28-2019, 06:49 AM
Any help with this issue would be greatly appreciated.
I need help automating the definition of names in Excel using a VBA script. I'm running Excel 2016.
I have a series of strings that represent the location of time and volume data in different tabs that I would like to graph. I want to iterate through my list of times and volumes and assign names for each samples time (e.g. S1time) and volume (S1vol) so that I can graph S1time vs. S1vol. To do this, when defining the name, I'd like to use the indirect function referring to the time and volume location strings.
Here is an example working in a tab called "Graph Summary":
Cell A1 is my time data string for sample 1 located in tab "Sample 1"
'Sample 1'!$AD$74:$AD$167
I want the name for A1 to be S1time = indirect(A1) and I'd like to do this for all of my samples.
In VBA, I keep track of the current sample number in a for loop by the variable v (for this example assume v=1)
time_name = "S" & v & "time"
loc1_string = "=indirect(" & "A" & v & ")"
ActiveWorkbook.Names.Add Name:=time_name, RefersToR1C1:= _
loc1_string
The result of this code is to set S1time = indirect('A1') which is not what I want.
If I manually go into the name manager and delete the ' around A1 it autocorrects to:
S1time = indirect('Graph Summary'!A1)
I learned that if I leave it like this, the A1 value changes as the sheet updates so I really need it to say:
S1time = indirect('Graph Summary'!$A$1)
How do I get this to happen?
If I modify the code to:
time_name = "S" & v & "time"
loc1_string = "=indirect(" & "$A$" & v & ")"
ActiveWorkbook.Names.Add Name:=time_name, RefersToR1C1:= _
loc1_string
I get an error that says: Run-time error '1004': There's a problem with this formula.
Is there a way to get the name to be defined as I'd like?
I need help automating the definition of names in Excel using a VBA script. I'm running Excel 2016.
I have a series of strings that represent the location of time and volume data in different tabs that I would like to graph. I want to iterate through my list of times and volumes and assign names for each samples time (e.g. S1time) and volume (S1vol) so that I can graph S1time vs. S1vol. To do this, when defining the name, I'd like to use the indirect function referring to the time and volume location strings.
Here is an example working in a tab called "Graph Summary":
Cell A1 is my time data string for sample 1 located in tab "Sample 1"
'Sample 1'!$AD$74:$AD$167
I want the name for A1 to be S1time = indirect(A1) and I'd like to do this for all of my samples.
In VBA, I keep track of the current sample number in a for loop by the variable v (for this example assume v=1)
time_name = "S" & v & "time"
loc1_string = "=indirect(" & "A" & v & ")"
ActiveWorkbook.Names.Add Name:=time_name, RefersToR1C1:= _
loc1_string
The result of this code is to set S1time = indirect('A1') which is not what I want.
If I manually go into the name manager and delete the ' around A1 it autocorrects to:
S1time = indirect('Graph Summary'!A1)
I learned that if I leave it like this, the A1 value changes as the sheet updates so I really need it to say:
S1time = indirect('Graph Summary'!$A$1)
How do I get this to happen?
If I modify the code to:
time_name = "S" & v & "time"
loc1_string = "=indirect(" & "$A$" & v & ")"
ActiveWorkbook.Names.Add Name:=time_name, RefersToR1C1:= _
loc1_string
I get an error that says: Run-time error '1004': There's a problem with this formula.
Is there a way to get the name to be defined as I'd like?