Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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" wrote in message ups.com... 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!S ECOND,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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_tex t_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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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 -- Don Guillett SalesAid Software "Rosencrantz" wrote in message oups.com... 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_tex t_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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to make named ranges
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,CO UNTA(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,COU NTA(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to named ranges in a macro | Excel Programming | |||
How do I get a macro to ask for a 'range' to go to with a choice of Named ranges? | Excel Programming | |||
Macro (Print) - Calling Named Ranges | Excel Discussion (Misc queries) | |||
Macro eliminating named ranges | Excel Programming | |||
Named Ranges - Macro Problems | Excel Programming |