joncarlos5
01-23-2014, 01:45 PM
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.
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.