replacing characters in a string

[Robert H]

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.

 

[Rick Rothstein]

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.

 

[Joel]

zNmRngA is a range just like C is a range object.

So instead of

C.Value = Replace(C.Value, " ", "_") 'Make header text

Use

zNmRngA.Value = Replace(zNmRngA.Value, " ", "_") 'Make header text

 

[Robert H]

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