Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
Hi Gurus,
I'm trying to find a way for Excel to lump various columns into different named ranges based on the names or headings of the columns. There will be five different types of columns that will repeat horizontally, eventually extending to about column 200, and each column with the same heading should be included in its corresponding named range. Instead of manually having to do something like... Range("U:U,Y:Y").Select Range("U:U,Y:Y,AC:AC").Select Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO").Select Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW").Sel ect Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW,BA:BA ").Select Can Excel do it automatically? Thanks, Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
Since the data is laid out nicely, I'd just count starting at the first column
and ending at the last. I used column U as the first and BA as the last. Option Explicit Option Base 0 Sub testme() Dim myRngs As Variant Dim iCtr As Long Dim rCtr As Long Dim myNames As Variant Dim myStep As Long myNames = Array("Name01", "Name02", "Name03", "Name04", "Name05") ReDim myRngs(LBound(myNames) To UBound(myNames)) For rCtr = LBound(myRngs) To UBound(myRngs) Set myRngs(rCtr) = Nothing Next rCtr myStep = UBound(myNames) - LBound(myNames) + 1 With ActiveSheet For iCtr = .Range("u1").Column To .Range("BA1").Column Step myStep For rCtr = LBound(myRngs) To UBound(myRngs) If myRngs(rCtr) Is Nothing Then Set myRngs(rCtr) = .Cells(1, iCtr + rCtr) Else Set myRngs(rCtr) _ = Union(myRngs(rCtr), .Cells(1, iCtr + rCtr)) End If Next rCtr Next iCtr End With For rCtr = LBound(myRngs) To UBound(myRngs) myRngs(rCtr).EntireColumn.Name = myNames(rCtr) Next rCtr End Sub Arnold wrote: Hi Gurus, I'm trying to find a way for Excel to lump various columns into different named ranges based on the names or headings of the columns. There will be five different types of columns that will repeat horizontally, eventually extending to about column 200, and each column with the same heading should be included in its corresponding named range. Instead of manually having to do something like... Range("U:U,Y:Y").Select Range("U:U,Y:Y,AC:AC").Select Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO").Select Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW").Sel ect Range("U:U,Y:Y,AC:AC,AJ:AJ,AO:AO,AS:AS,AW:AW,BA:BA ").Select Can Excel do it automatically? Thanks, Arnold -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
Hi Dave,
Thanks for the reply and code. The columns will, however, not be laid out so nicely--that is, they will not go in any particular order horizontally across the sheet. Also, there will be more of some columns than others. Will the code account for this? I'll try it as soon as I can. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
Option Explicit
Sub testme() Dim myRng As Range Dim myTitles As Variant Dim myNames As Variant Dim iCtr As Long Dim FoundCell As Range Dim FirstAddress As String myNames = Array("Name01", "Name02", "Name03", "Name04", "Name05") myTitles = Array("a", "what you want1", "another one", _ "next one", "last one here") If UBound(myNames) < UBound(myTitles) Then MsgBox "design error!" Exit Sub End If With ActiveSheet For iCtr = LBound(myTitles) To UBound(myTitles) FirstAddress = "" Set myRng = Nothing With .Rows(1) 'row with header Set FoundCell = .Cells.Find(what:=myTitles(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox myTitles(iCtr) & " wasn't found!" Else FirstAddress = FoundCell.Address Set myRng = FoundCell Do Set FoundCell = .FindNext(after:=FoundCell) If FoundCell.Address = FirstAddress Then Exit Do End If Set myRng = Union(myRng, FoundCell) Loop myRng.EntireColumn.Name = myNames(iCtr) End If End With Next iCtr End With End Sub Arnold wrote: Hi Dave, Thanks for the reply and code. The columns will, however, not be laid out so nicely--that is, they will not go in any particular order horizontally across the sheet. Also, there will be more of some columns than others. Will the code account for this? I'll try it as soon as I can. Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
Thanks Dave--it worked great.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
One more question,
I have a question about the use of these ranges....I was wanting to do the above so that I could automatically get the addresses of all the cells in all the columns for a certain thing, like "Days". However, I need to be able to do tallies and other simple formulas across the sheet for individual rows (1 person per row). For instance, one of the ranges of columns was named "Day", and for every Day column that was included in the range, add the values of the cells (0, 1, 2, 3, 4, or 5), skipping any cells that are all text (ie. x or X). Then I could copy this formula down for each person. This would give each person's total score for all the days they were present (ommitting the "x" days). Is there a way to get the range from the Define Name into a formula, but replacing entire $columns with just the cells in the columns across one row. Does this make sense? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define Names in Workbook through Code
You could use range names (but that sounds pretty awkward (to me, anyway)).
Maybe better would be to use =sumif() =sumif($a$1:$AB$1,"Some title",$a2:$ab2) Arnold wrote: One more question, I have a question about the use of these ranges....I was wanting to do the above so that I could automatically get the addresses of all the cells in all the columns for a certain thing, like "Days". However, I need to be able to do tallies and other simple formulas across the sheet for individual rows (1 person per row). For instance, one of the ranges of columns was named "Day", and for every Day column that was included in the range, add the values of the cells (0, 1, 2, 3, 4, or 5), skipping any cells that are all text (ie. x or X). Then I could copy this formula down for each person. This would give each person's total score for all the days they were present (ommitting the "x" days). Is there a way to get the range from the Define Name into a formula, but replacing entire $columns with just the cells in the columns across one row. Does this make sense? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Names in Excel | Excel Discussion (Misc queries) | |||
Delete Define names across workbook | Excel Worksheet Functions | |||
alternate UI for Define Names ?? | Excel Discussion (Misc queries) | |||
how to use VB code to define vlookup in two workbook | Excel Programming | |||
Define Names / RefersToRange | Excel Programming |