adding named ranges without a "-"

[mark kubicki]

I'm writing a sub that works thru a worksheet adding named ranges for all of
the values in a given column (the values are person's names); and I am
naming these "named ranges" effectively the same as the person's name
(ex: the person's name might be "Tom", so the range is named
"Proj_mngr_Tom")

one of the values person's name has a "-" (Ju-Li) which is causing an
error...

is there away of stripping the "-"? (I am presuming I'll need to write a
sub that cycles thru each letter, checking its value...)

thank in advance
-mark

 

[RB Smissaert]

This is a function I use to make valid range names:

Function MakeValidRangeName(ByVal strRangeName As String, _
Optional strAddIfTrailingNumerics As String =
"_") As String

Dim strInvalidChars
Dim i As Byte

If Len(strRangeName) = 0 Then
MakeValidRangeName = "No_Name_Provided"
Exit Function
End If

'delete leading numerics
'-----------------------
strRangeName = ClearLeadingNumerics(strRangeName)

'note that the characters \ and . are valid
'------------------------------------------
strInvalidChars = Array("!", "£", "$", "%", "%", "^", "&", _
"*", "(", ")", "-", "+", "=", "{", _
"}", "[", "]", ":", ";", "@", "'", _
"~", "#", "|", "<", ",", ">", "?", _
"/")

strRangeName = Replace(strRangeName, " ", "_", 1, -1, vbBinaryCompare)

For i = 0 To UBound(strInvalidChars)
strRangeName = Replace(strRangeName, _
strInvalidChars(i), _
"", _
1, _
-1, _
vbBinaryCompare)
Next i

'to avoid range names ending with numerics
'-----------------------------------------
If Asc(Right$(strRangeName, 1)) > 47 And _
Asc(Right$(strRangeName, 1)) < 58 Then
strRangeName = strRangeName & strAddIfTrailingNumerics
End If

strRangeName = Left$(strRangeName, 255)

MakeValidRangeName = strRangeName

End Function


RBS

 

[Guest]

You can evaluate the individuals name with the REPLACE function.
REPLACE(NameValue,FindString,Replacestring)

Replace(NameValue, "-","_")

 

[RB Smissaert]

Forgot to post the function to clear leading numerics:

Function ClearLeadingNumerics(strString As String) As String

Do While Asc(Left(strString, 1)) > 47 And _
Asc(Left(strString, 1)) < 58
strString = Mid(strString, 2)
Loop

ClearLeadingNumerics = strString

End Function


RBS

This is a function I use to make valid range names:

Function MakeValidRangeName(ByVal strRangeName As String, _
Optional strAddIfTrailingNumerics As String =
"_") As String

Dim strInvalidChars
Dim i As Byte

If Len(strRangeName) = 0 Then
MakeValidRangeName = "No_Name_Provided"
Exit Function
End If

'delete leading numerics
'-----------------------
strRangeName = ClearLeadingNumerics(strRangeName)

'note that the characters \ and . are valid
'------------------------------------------
strInvalidChars = Array("!", "£", "$", "%", "%", "^", "&", _
"*", "(", ")", "-", "+", "=", "{", _
"}", "[", "]", ":", ";", "@", "'", _
"~", "#", "|", "<", ",", ">", "?", _
"/")

strRangeName = Replace(strRangeName, " ", "_", 1, -1, vbBinaryCompare)

For i = 0 To UBound(strInvalidChars)
strRangeName = Replace(strRangeName, _
strInvalidChars(i), _
"", _
1, _
-1, _
vbBinaryCompare)
Next i

'to avoid range names ending with numerics
'-----------------------------------------
If Asc(Right$(strRangeName, 1)) > 47 And _
Asc(Right$(strRangeName, 1)) < 58 Then
strRangeName = strRangeName & strAddIfTrailingNumerics
End If

strRangeName = Left$(strRangeName, 255)

MakeValidRangeName = strRangeName

End Function


RBS

I'm writing a sub that works thru a worksheet adding named ranges for all
of the values in a given column (the values are person's names); and I am
naming these "named ranges" effectively the same as the person's name
(ex: the person's name might be "Tom", so the range is named
"Proj_mngr_Tom")

one of the values person's name has a "-" (Ju-Li) which is causing an
error...

is there away of stripping the "-"? (I am presuming I'll need to write a
sub that cycles thru each letter, checking its value...)

thank in advance
-mark

 

[Dave Peterson]

Dim myStr As String
myStr = "Ju-Li"
'xl2k or higher
myStr = Replace(myStr, "-", "_")
'or before xl2k
'myStr = Application.Substitute(myStr, "-", "_")
MsgBox myStr


For just that one problem.