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

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

(E-Mail Removed) wrote:
> I’m trying to look through a header row and for each column aftera
> 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

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."
>
>
> "(E-Mail Removed)" wrote:
>
> > 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 followingcode
> > 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
> >
> >