Rick I was able to make both of those examples work in my
application. Both are much cleaner (efficient) ways to do what I was
doing, especialy the creating the names. I cant believe i was doing
that the "long way"...
Muchos Gracias!
Robert

On Jan 5, 12:18*pm, "Rick Rothstein"
wrote:
First off... you don't have to change the value of the cell just to use its
value in another assignment statement. This code should create the Named
Ranges for you...

* For Each C In Selection
* * cAdd = C.Address
* * cRng = Range(C.Offset(1, 0), C.End(xlDown)).Address
* * ActiveSheet.Names.Add Name:=Replace(C.Value, " ", "_"), _
* * * * * * * * * * * * * RefersTo:="=" & "'" & SheetName & _
* * * * * * * * * * * * * * * * * * "'" & "!" & cRng
* Next

As for your "Find" question... don't search the cells... search the Names
collection instead.

* For Each N In Names
* * If UCase(N.Name) Like "*IMP*" Then
* * * Set zNmRngA = N.RefersToRange
* * * Exit For
* * End If
* Next

Note: The text being searched for must all be in upper case letters between
the asterisks in the If..Then statement.

--
Rick (MVP - Excel)

"Robert H" wrote in message

...



In a data analysis worksheet I create named ranges for each data
column. The names are used in formulas, conbditional formating, charts
etc. *To work around named range "name" limitations and how I want my
column headings to look I replace blank spaces with "_", name the
range and then replace "_" with blank spaces.
Note, the renaming is new I have been just living with underscores in
the headings.

Like so:
* *For Each C In Selection
* * * *cAdd = C.Address
* * * *cRng = Range(C.Offset(1, 0), C.End(xlDown)).Address

* * * * * *C.Value = Replace(C.Value, " ", "_") *'Make header text
safe for "NAMES"
* * * *ActiveSheet.Names.Add Name:=C.Value, RefersTo:="=" & "'" &
SheetName & "'" & "!" & cRng

* * * * * *C.Value = Replace(C.Value, "_", " ") * 'restore blanks in
header text
* *Next

In a few instances the code searches through the headings for keywords
and the selects a named range based in the findings. *Like so:

* * * Set zNmRngA = Cells.Find(what:="IMP", _
* * * * * * * * After:=ActiveCell, _
* * * * * * * * LookIn:=xlValues, _
* * * * * * * * lookat:=xlPart, _
* * * * * * * * searchorder:=xlByColumns, _
* * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * MatchCase:=False, _
* * * * * * * * SearchFormat:=False)

in this case the first column found has the heading "IMP 100 Hz" when
it tries to match it to a name one is not found because the
corresponding name is "IMP_100_Hz"

is it possible to modify zNmRngA after it is set to find and replace
the blanks with "_"?

I know this is a goofy setup but it has evolved over the years and Im
just trying to clean up the presentation.- Hide quoted text -

- Show quoted text -