macro to make named ranges

[Rosencrantz]

Hi everyone,

I put this post under a different title, so I decided to try it with a
different subject.

So, I'm making a macro that makes named ranges when the user inputs the
column numbers and then the names of the two ranges. Here's the code as
of now:

Sub update()

Dim columns() As String
Dim length As Integer
Dim X As String
Dim y As String
Dim first As Integer
Dim second As Integer
Dim length2 As Integer
Dim names() As String
Dim name1 As String
Dim name2 As String

Do
output = InputBox("Which column(s)? (up to two and in numeric
value)", "Ranges", "3, 4", , , "c:\Windows\Help\Procedure Help.hlp", 0)
length = Len(output)
Loop Until length < 8

If length > 0 Then
columns = Split(output)

X = columns(0)
y = columns(1)

first = Val(X)
second = Val(y)

output2 = InputBox("First name?", "Ranges", "first", , ,
"c:\Windows\Help\Procedure Help.hlp", 0)
name1 = output2

output3 = InputBox("Second name?", "Ranges", "second", , ,
"c:\Windows\Help\Procedure Help.hlp", 0)
name2 = output3

Range(Cells(8, first), Cells(Rows.count, first).End(xlUp)).Name = name1
Range(Cells(8, second), Cells(Rows.count, second).End(xlUp)).Name =
name2

Else
End If

End Sub

It works, but I want to make it more user friendly. My final goal is to
make it work like this type of named range:

=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)

So that it will automatically be updated when new data is put in the
columns, along with any graphs and equations that use the named range.
At the moment, I have to go into the graphs and manually change the
code from this:

=SERIES(,grnd_results_d1_text_file.txt!$G$8:$G$56,grnd_results_d1_text_file.txt!$H$8:$H$56,1)

to this:

=SERIES(,grnd_results_d1_text_file.txt!FIRST,grnd_results_d1_text_file.txt!SECOND,1)

I want to avoid this typing and code the macro in such a way that the
graphs will automatically use the named range of the columns called in
the graph.

Also, when new data is added, at the moment I have to rerun the macro.
This could lead to typing error in the names fo the ranges so the
graphs may not update.

Any thoughts on these? I'm new to VBA so I may be missing something
obvious, so any help is greatly appreciated!

Thanks in advance!
~M

 

[Bob Phillips]

Is this what you want?

Sub update()
Dim columns() As String
Dim length As Integer
Dim X As String
Dim y As String
Dim first As Integer
Dim second As Integer
Dim length2 As Integer
Dim names() As String
Dim name1 As String
Dim name2 As String
Dim output, output2, output3

Do
output = InputBox("Which column(s)? (up to two and in numeric value)", _
"Ranges", "3, 4", , , "c:\Windows\Help\Procedure
Help.hlp", 0)
length = Len(output)
Loop Until length < 8

If length > 0 Then
columns = Split(output)

X = columns(0)
y = columns(1)

first = Val(X)
second = Val(y)

output2 = InputBox("First name?", "Ranges", "first", , , _
"c:\Windows\Help\Procedure Help.hlp", 0)
name1 = output2

output3 = InputBox("Second name?", "Ranges", "second", , , _
"c:\Windows\Help\Procedure Help.hlp", 0)
name2 = output3

With ActiveWorkbook.names
.Add Name:=name1, _
RefersToR1C1:="=OFFSET(Sheet1!R1C" & first & _
",1,0,COUNTA(C" & first & ":C" & first & ")-1)"
.Add Name:=name2, _
RefersToR1C1:="=OFFSET(Sheet1!R1C" & second & _
",1,0,COUNTA(C" & second & ":C" & second & ")-1)"
End With

Else
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rosencrantz]

Hmmm...unfortunately, it appears to be performing exactly the same way
as my original code does, unless I'm not looking for the effect of the
new lines in the right places.

~M

 

[Don Guillett]

I'm sure this is NOT what you want but you get the idea
Sub createdefinednamerange()
col1 = InputBox("enter 1st column letter")
col2 = InputBox("enter lastcol")
ms = "=offset(sheet1!$" & col1 & "$1,1,0,counta($" & col2 & ":$" & col2 &
")-1)"
ActiveWorkbook.Names.Add Name:="ser1", RefersTo:=ms
End Sub

then you use in the chart as
=yourworkbook.xls!ser1

 

[Rosencrantz]

Hmmm...I tried adjusting the code of the first thing you gave me, but I
still can't get it to update when new data is added. If you have any
other ideas or know where I can go to for some ideas, please let me
know and thanks for your help!

~M

 

[Rosencrantz]

Never mind, I got it to work-I left in lines from my own code in the
code you sent me, so it messed up the ranges. But now, when I call the
macro, the range becomes:

first =
OFFSET(Sheet1!$C:$C,1,0,COUNTA(grnd_results_d1_text_file.txt!$C:$C)-1)

Instead of

first = OFFSET(grnd_results_d1_text_file.txt!$C:$8, 1, 0, _
COUNTA(grnd_results_d1_text_file.txt!$C:$C)-1)

So, I'm trying to mess around with it to get it to be the second one.
If you have any ideas of how I can tweak the lines:

With ActiveWorkbook.names
.Add Name:=name1, RefersToR1C1:="=OFFSET(Sheet1!C" & first & _
",1,0,COUNTA(C" & first & ":C" & first & ")-1)"
.Add Name:=name2, RefersToR1C1:="=OFFSET(Sheet1!C" & second & _
",1,0,COUNTA(C" & second & ":C" & second & ")-1)"
End With

I would greatly appreciate it!

~M

 

[Don Guillett]

I was trying to illustrate that you can make substitutions. If you execute
the code from the sheet where the name will go then you can just leave out
the sheet! part. Excel should fill it in for you, where appropriate.

Try changing my code instead of substituting yours.

Sub createdefinednamerange()
col1 = InputBox("enter 1st column letter")
col2 = InputBox("enter lastcol")
ms = "=offset($" & col1 & "$1,1,0,counta($" & col2 & ":$" & col2 &
")-1)"
ActiveWorkbook.Names.Add Name:="ser1", RefersTo:=ms
End Sub

Here is another idea where you could substitute inputbox for =1
Sub makename()
fr = 1
lr = 4
fc = 2
lc = 5
With Sheets("sheet6")
.Range(.Cells(fr, fc), .Cells(lr, lc)).Name = "Bill"
End With
End Sub

or even this but not dynamic so executed when needed
Sub makename()
With Sheets("sheet6")
fr = 1
lr = Application.CountA(.Columns(1))
fc = 2
lc = 5
.Range(.Cells(fr, fc), .Cells(lr, lc)).Name = "Billa"
End With
End Sub

 

[Rosencrantz]

Thanks for your continued help! I tried the code you gave me, and it
came up with weird errors when I tried to run it. Before I saw your
post, though, I got to the point where when I run the macro, the named
range is:

=OFFSET(grnd_results_d1_text_file.txt!$C:$C,1,0,COUNTA(grnd_results_d1_text_file.txt!$C:$C)-1)

But, now it needs to be:

=OFFSET(grnd_results_d1_text_file.txt!$C$8,1,0,COUNTA(grnd_results_d1_text_file.txt!$C:$C)-1)

So, I need to chance $C:$C to $C$8. I'm looking at the code:

With ActiveWorkbook.names
.Add Name:=name1, RefersToR1C1:="=OFFSET(" & sheet & "!C" & first &
",1,0,COUNTA(C" & first & ":C" & first & ")-1)"
.Add Name:=name2, RefersToR1C1:="=OFFSET(" & sheet & "!C" & second
& ",1,0,COUNTA(C" & second & ":C" & second & ")-1)"
End With

And I can't figure out why that happens in the first place and how to
stop it. Once I get this solved, then it will work properly in the
graphs, and then I just have to worry about figuring out how to make
the graphs automatically use the named ranges.

Thanks, again, for your continued help!
~M