Naming a Range...?
 

I have this change event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.count 1 then exit sub
if not intersect(target,Range("G10:G50")) is nothing then
if Not isempty(Target) then
Target.Offset(0,1).Resize(1,4).Name =
application.substitute(Target," ","_")
end if
End if
End Sub

Is there a way to ensure that the macro can convert any name into the
name of a range?

ex. "Excavator 450 (Hitachi)", "Front-end Loader 300"

Naming a Range...?
 

Use application.Substitute to replace each illegal character that might be
contained.

But then is the name useful anymore?

--
Regards,
Tom Ogilvy


"mastermind" wrote:

I have this change event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.count 1 then exit sub
if not intersect(target,Range("G10:G50")) is nothing then
if Not isempty(Target) then
Target.Offset(0,1).Resize(1,4).Name =
application.substitute(Target," ","_")
end if
End if
End Sub

Is there a way to ensure that the macro can convert any name into the
name of a range?

ex. "Excavator 450 (Hitachi)", "Front-end Loader 300"

Naming a Range...?
 

I would like to do that, but I don't know how write it out. I got that
last little bit of code from someone on this site. I also don't know
what all the illegal characters are.

John

Naming a Range...?
 

Guidelines for names
What characters are allowed? The first character of a name must be a letter
or an underscore character. Remaining characters in the name can be letters,
numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell reference,
such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed. Underscore
characters and periods may be used as word separators€” for example, Sales_Tax
or First.Quarter.

How many characters can be used? A name can contain up to 255 characters.

Note If a name defined for a range contains more than 253 characters, you
cannot select it from the Name box.

Are names case sensitive? Names can contain uppercase and lowercase letters.
Microsoft Excel does not distinguish between uppercase and lowercase
characters in names. For example, if you have created the name Sales and then
create another name called SALES in the same workbook, the second name will
replace the first one.

---------------
I guess you would have to write a set of rules to convert any string into an
acceptable name.

--
Regards,
Tom Ogilvy


"mastermind" wrote:

I would like to do that, but I don't know how write it out. I got that
last little bit of code from someone on this site. I also don't know
what all the illegal characters are.

John