Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Named ranged for each column...
Im trying to look through a header row and for each column after a
certain point (the first few columns do not require analysis), assign named ranges to the column data using the first row as the name. I want to do this for all the remaining columns. I have three problems in my code. Three, that I can see that is At the line - For Each c In Selection, I think I should use For Each c In Selection however, I receive the error: error 13 type mismatch At the line - For Each c In Selection I expect the following code to act on each cell in the selection, in sequence however, it happens to the entire selection Using the macro recorder I came up with ActiveSheet.Names.Add Name:="DC_RES", RefersToR1C1:="=R1C7:R7C7" Ive tweaked that to: ActiveSheet.Names.Add Name:=c.Value, refersto c.address But I dont have a handle on the refers to Property My code is: Public Sub CreateNames() Dim c Dim srchRow 'designate header range Range("a1").Select Range(Selection, Selection.End(xlToRight)).Select srchRow = Selection.Find(what:="DC_RES", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Select srchRow = Range(Selection, Selection.End(xlToRight)).Select For Each c In Selection 'srchRow causes an error! ActiveSheet.Names.Add Name:=c.Value, refersto c.address Next End Sub any help will be appreciated Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Named ranged for each column...
I ended up beating the code into working with this:
Dim srchRow Dim SheetName As String Dim cRng 'As Range Dim cAdd SheetName = ActiveSheet.Name 'designate header range Range("a1").Select Range(Selection, Selection.End(xlToRight)).Select srchRow = Selection.Find(What:="DC_RES", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Select srchRow = Range(Selection, Selection.End(xlToRight)).Select For Each c In Selection 'srchRow causes an error! cAdd = c.Address 'cRng = Empty cRng = Range(c.Offset(1, 0), c.End(xlDown)).Address ActiveSheet.Names.Add Name:=c.Value, RefersTo:="=" & SheetName & "!" & cRng Next Both of you offered much cleaner methods Thanks for the help Robert Charles Chickering wrote: Dim c As Range For Each c In Selection.Cells Try that. -- Charles Chickering "A good example is twice the value of good advice." " wrote: Im trying to look through a header row and for each column after a certain point (the first few columns do not require analysis), assign named ranges to the column data using the first row as the name. I want to do this for all the remaining columns. I have three problems in my code. Three, that I can see that is At the line - For Each c In Selection, I think I should use For Each c In Selection however, I receive the error: error 13 type mismatch At the line - For Each c In Selection I expect the following code to act on each cell in the selection, in sequence however, it happens to the entire selection Using the macro recorder I came up with ActiveSheet.Names.Add Name:="DC_RES", RefersToR1C1:="=R1C7:R7C7" Ive tweaked that to: ActiveSheet.Names.Add Name:=c.Value, refersto c.address But I dont have a handle on the refers to Property My code is: Public Sub CreateNames() Dim c Dim srchRow 'designate header range Range("a1").Select Range(Selection, Selection.End(xlToRight)).Select srchRow = Selection.Find(what:="DC_RES", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Select srchRow = Range(Selection, Selection.End(xlToRight)).Select For Each c In Selection 'srchRow causes an error! ActiveSheet.Names.Add Name:=c.Value, refersto c.address Next End Sub any help will be appreciated Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Adding Time based on a name ranged | Excel Discussion (Misc queries) | |||
Named ranged selection problem | Excel Worksheet Functions | |||
Changing a named ranged based on array size on a form | Excel Programming | |||
Ranges, adding a column to a named print range then saving the file | Excel Programming | |||
Vlookup - Using a named ranged for col_index_num | Excel Discussion (Misc queries) |