PDA

View Full Version : [SOLVED] Saving Symbols as a variable



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.

Paul_Hossler
01-23-2014, 05:17 PM
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
01-24-2014, 03:46 AM
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
01-24-2014, 05:07 AM
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
01-24-2014, 07:26 AM
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!