swaggerbox
01-03-2020, 03:12 AM
I have code below that reformats a certain string. I would like to transfer the replace parameters from the code to the range (Sheet2) so it would be easier for me to add the parameter instead of editing it in the code. Column A in Sheet2 are the words to replace while Column B in Sheet2 are the replacement terms. See attached workbook.
Could anyone lend me a helping hand?
Sub ProcessMar()
Dim myString As String
myString = ActiveSheet.TextBox1
myString = Replace(myString, " is a ", "=")
myString = Replace(myString, " is ", "=")
myString = Replace(myString, " to ", "-")
myString = Replace(myString, "selected from the group consisting of", "including")
myString = Replace(myString, "; ", "#")
myString = Replace(myString, "R1", "R_1")
myString = Replace(myString, "R2", "R_2")
myString = Replace(myString, "R3", "R_3")
myString = Replace(myString, "R4", "R_4")
myString = Replace(myString, "R5", "R_5")
myString = Replace(myString, "R6", "R_6")
myString = Replace(myString, "R7", "R_7")
myString = Replace(myString, "R8", "R_8")
myString = Replace(myString, "R9", "R_9")
myString = Replace(myString, "NO2", "NO_2")
myString = Replace(myString, "CH2", "CH_2")
myString = Replace(myString, "CH3", "CH_3")
myString = Replace(myString, ", and", "#")
myString = Replace(myString, "; and", "#")
myString = Replace(myString, "selected from one or more of ", "")
myString = Replace(myString, "hydrogen", "H")
myString = Replace(myString, "cyano", "CN")
myString = Replace(myString, "nitro", "NO_2")
myString = Replace(myString, "hydroxy", "OH")
myString = Replace(myString, "mercapto", "SH")
myString = Replace(myString, "methoxy", "OMe")
myString = Replace(myString, "trifluoromethyl", "CF3")
myString = Replace(myString, "trifluoromethoxy", "OCF3")
myString = Replace(myString, " are each independently including ", "=")
myString = Replace(myString, "halogen", "halo")
myString = Replace(myString, ":", "")
myString = Replace(myString, "#and ", "and#")
myString = "<MAR>" & myString & "</MAR>"
Dim lasPos As Integer
lasPos = InStrRev(myString, "#")
leftString = Left(myString, lasPos - 1)
leftString = leftString & "; and#"
rightString = Mid(myString, lasPos + 1, Len(myString))
myString = leftString & " " & rightString
myString = Replace(myString, "# ", "#")
ActiveSheet.TextBox2 = myString
End Sub
Could anyone lend me a helping hand?
Sub ProcessMar()
Dim myString As String
myString = ActiveSheet.TextBox1
myString = Replace(myString, " is a ", "=")
myString = Replace(myString, " is ", "=")
myString = Replace(myString, " to ", "-")
myString = Replace(myString, "selected from the group consisting of", "including")
myString = Replace(myString, "; ", "#")
myString = Replace(myString, "R1", "R_1")
myString = Replace(myString, "R2", "R_2")
myString = Replace(myString, "R3", "R_3")
myString = Replace(myString, "R4", "R_4")
myString = Replace(myString, "R5", "R_5")
myString = Replace(myString, "R6", "R_6")
myString = Replace(myString, "R7", "R_7")
myString = Replace(myString, "R8", "R_8")
myString = Replace(myString, "R9", "R_9")
myString = Replace(myString, "NO2", "NO_2")
myString = Replace(myString, "CH2", "CH_2")
myString = Replace(myString, "CH3", "CH_3")
myString = Replace(myString, ", and", "#")
myString = Replace(myString, "; and", "#")
myString = Replace(myString, "selected from one or more of ", "")
myString = Replace(myString, "hydrogen", "H")
myString = Replace(myString, "cyano", "CN")
myString = Replace(myString, "nitro", "NO_2")
myString = Replace(myString, "hydroxy", "OH")
myString = Replace(myString, "mercapto", "SH")
myString = Replace(myString, "methoxy", "OMe")
myString = Replace(myString, "trifluoromethyl", "CF3")
myString = Replace(myString, "trifluoromethoxy", "OCF3")
myString = Replace(myString, " are each independently including ", "=")
myString = Replace(myString, "halogen", "halo")
myString = Replace(myString, ":", "")
myString = Replace(myString, "#and ", "and#")
myString = "<MAR>" & myString & "</MAR>"
Dim lasPos As Integer
lasPos = InStrRev(myString, "#")
leftString = Left(myString, lasPos - 1)
leftString = leftString & "; and#"
rightString = Mid(myString, lasPos + 1, Len(myString))
myString = leftString & " " & rightString
myString = Replace(myString, "# ", "#")
ActiveSheet.TextBox2 = myString
End Sub