Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding named ranges without a "-"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding named ranges without a "-"
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 "mark kubicki" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding named ranges without a "-"
You can evaluate the individuals name with the REPLACE function.
REPLACE(NameValue,FindString,Replacestring) Replace(NameValue, "-","_") -- Kevin Backmann "mark kubicki" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding named ranges without a "-"
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 "RB Smissaert" wrote in message ... 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 "mark kubicki" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding named ranges without a "-"
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. mark kubicki wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i "link" two sets of named ranges | Excel Discussion (Misc queries) | |||
Adding hh:mm times in a row as part of a "named" cell | Excel Worksheet Functions | |||
Defining Multiple "named" data ranges for Piot Tables in Excel 200 | Excel Discussion (Misc queries) | |||
No idea what "Named Ranges" are. | Excel Discussion (Misc queries) | |||
a lot of "ExternalData" Named Ranges | Excel Programming |