PDA

View Full Version : Defining names in VBA using for loop



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?

JH987
08-28-2019, 01:43 PM
Just to possibly simplify this. Why doesn't this code work:

v=1
time_name = S1time

loc1_string = "=indirect(" & "'Graph Summary'!" & "$A$" & v & ")"
ActiveWorkbook.Names.Add Name:=time_name, RefersToR1C1:= _
loc1_string


It seems to me this should assign S1time as =indirect('Graph summary'!$A$1) which is what I'm looking for but I get a code 1004 error.

Thanks.

snb
08-29-2019, 12:50 AM
Use intelligent tables. They will be 'named' automatically and are dynamic.

And naming can be done in VBA:



Sub M_snb()
for j=0 to 10
range("A1:A10").Offset(,j).name="snb_" & format(j,"000")
next
End Sub

JH987
08-29-2019, 05:16 AM
Thanks for the reply. Smart tables does look useful for keeping graphs updated. I'm still looking for a solution where I have a table that includes the address for my x-axis in column A and y-axis in column B for several series. I want my scatter plot to update based on those addresses. Is there a different way to do this than using names with indirect functions?

If I create the names manually, my method works. I just want a way to automate creating names that have =indirect('Graph Summary'!$A$2) as the definition of the name. I included an example in the image below.

24895

Jan Karel Pieterse
08-29-2019, 05:30 AM
Your last example doesn't work because you are using ReferstoR1C1 and should be using RefersTo.

Jan Karel Pieterse
08-29-2019, 05:32 AM
Does this (ancient!) download from my website help? https://jkp-ads.com/download.asp#autochrt

JH987
08-29-2019, 05:47 AM
That worked! Changing it to RefersTo fixed the issue. Thank you for the help.