Adding Named ranged for each column...

[robert.hatcher]

I’m 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 don’t 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

 

[Scott]

I didn't actually look through you code. (A bit lazy this morning...)

Here's a sample that does what you're trying to do. (I hard-coded it
to go from column C to F (3 to 6), but that's easy to fix) You'll
probably want to do something similar... determine the extent of your
range (which columns it covers), and just loop through "FOR i = Start
TO Finish" instead of the FOR EACH.

Sub Test()
Dim i As Long
Dim SheetName As String
Dim WS As Worksheet

SheetName = "Sheet1"
Set WS = Worksheets(SheetName)

For i = 3 To 6
ActiveWorkbook.Names.Add _
Name:=WS.Cells(1, i), _
RefersToR1C1:="=" & SheetName & "!C" & i
Next i

End Sub

Scott

 

[robert.hatcher]

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