Saving Symbols as a variable

[joncarlos5]

Hello everyone and thank you in advance for any advice.

I have written a macro that renames each sheet in a workbook from a specific cell. Each sheet eventually becomes the number of the account contained in that sheet.

The problem I am having is that I want a user to be able to remove any symbols from the account name. Sometimes it will be wrapped in "quotes" other times in (parenthesis) sometimes with a #hashtag before the number. So I added an inputbox and ask for for the symbol to remove. However whenever I run the macro it never replaces said symbol. I tried every different declaration for the variable I can think of. Code below

Sub WorksheetLoop()
            
        Dim SName As String
        Dim WS_Count As Integer
        Dim I As Integer
        Dim CellRange As String
        Dim NRight As Integer
        Dim Symbol1 As String
        Dim Symbol2 As String
                
        
            'ask for cell range
            CellRange = InputBox("Input the cell that contains the label", "Enter Cell Range", "A3")
            
            'ask for Number of Char to include
            NRight = InputBox("Input the number of characters that you want to capture to the right including spaces", "Enter characters to capture", "7")
            
            'What symbols to exclude - Default is a space
            Symbol1 = InputBox("Input any symbol to exclude, if none leave blank", "Excluded symbol", " ")
            Symbol2 = InputBox("Input a second symbol to exclude, if none leave blank", "Excluded symbol 2", " ")

            WS_Count = ActiveWorkbook.Worksheets.Count
        
         'Begin the loop.
         For I = 1 To WS_Count

            SName = Sheets(I).Range(CellRange).Value
            SName = Right(SName, NRight)
            SName = Replace(SName, "Symbol1", " ")            
            SName = Replace(SName, "Symbol2", " ")

            'Replaces each symbol below in the string *until I figure out how to set a symbol as a string
            'SName = Replace(SName, ")", " ")
            'SName = Replace(SName, "(", " ")
            'SName = Replace(SName, ">", " ")
            'SName = Replace(SName, "<", " ")
            'SName = Replace(SName, "#", " ")
            'SName = Replace(SName, "$", " ")
            'SName = Replace(SName, "%", " ")
            'SName = Replace(SName, "&", " ")
            SName = Trim(SName)
            Sheets(I).Name = "Temp"
            Sheets(I).Name = SName
            
        Next I


      End Sub

For now I have come up with the above (commented out) workaround by including all symbols I think would be replaced.

[Paul_Hossler]

Welcome

A real quick look at

SName = Replace(SName, "Symbol1", " ") 
 SName = Replace(SName, "Symbol2", " ")

seems to be that you are literally replacing the string Symbol1 with a space


You probably intended to replace the value of the variable Symbol1 with a space

SName = Replace(SName, Symbol1, " ") 
SName = Replace(SName, Symbol2, " ")

Delete the quotes around Symbol1 and Symbol2 and see if that works

Paul

[westconn1]

an alternative, would be to allow the user to enter as many symbols as required in the inputbox, then remove all

    Symbol = InputBox("Input all symbol to exclude, if none leave blank", "Excluded symbol", " ") 
   
     
    WS_Count = ActiveWorkbook.Worksheets.Count 
     
     'Begin the loop.
    For I = 1 To WS_Count 
         
        SName = Sheets(I).Range(CellRange).Value 
        SName = Right(SName, NRight) 
        for mychar = 1 to len(symbol)
           sname = replace(sname, mid(symbol, mychar, 1), "")
        next

        SName = Trim(SName) 
        Sheets(I).Name = "Temp" 
        Sheets(I).Name = SName 
         
    Next I

[snb]

or

Sub M_snb()
    For j = 1 To 255
      If j < 31 Or j > 122 Then c00 = c00 & IIf(j Mod 12 = 0, vbLf, vbTab) & j & vbTab & Chr(j)
    Next
    
    Cells.Replace InputBox(c00, "Enter character number"), ""
End Sub

[joncarlos5]

Wow Guys. Thank you so much.

Paul thank you for the quick fix, you solved my particular problem.

To Westconn1 and snb thanks for the suggestions. I will implement one of them today.

Thank you for the warm welcome to the forum.
You guys rock!