Consulting

Results 1 to 8 of 8

Thread: VBA Replace From Code to Range

  1. #1

    VBA Replace From Code to Range

    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
    Attached Files Attached Files

  2. #2
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    Try:

    Sub ked()
        Dim i&, myStr$, lasPos&, leftString$, rightString$
        myStr = ActiveSheet.TextBox1
        With Sheet2
            For i = 1 To .Cells(.Rows.Count, 1).End(3).Row
                myStr = Replace(myStr, .Cells(i, 1), .Cells(i, 2))
            Next
        End With
        myStr = "<MAR>" & myStr & "</MAR>"   
        lasPos = InStrRev(myStr, "#")
        leftString = Left(myStr, lasPos - 1)
        leftString = leftString & "; and#"
        rightString = Mid(myStr, lasPos + 1, Len(myStr))
        myStr = leftString & " " & rightString
        myStr = Replace(myStr, "# ", "#")
        ActiveSheet.TextBox2 = myStr
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    Great! Thanks again Paulked!

  4. #4
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    Semper in excretia sumus; solum profundum variat.

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    I'd prefer:

    Private Sub CommandButton1_Click()
      sn = Sheet2.Cells(1).CurrentRegion
    
      With Sheet1
        .TextBox2 = "<MAR>" & .TextBox1 & "</MAR>"
        
        For j = 1 To UBound(sn)
          .TextBox2 = Replace(.TextBox2, sn(j, 1), sn(j, 2))
        Next
        .TextBox2 = Replace(Replace(.TextBox2, "#", "; and #"), "; and #", "#", , 1)
      End With
    End Sub
    NB. Put the code in the macromodule it belongs to: sheet1.
    Avoid worksheet interaction (reading / writing) as much as possible: use Arrays (like sn) to do this.
    Last edited by snb; 01-03-2020 at 04:37 AM.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,770
    I'd prefer:

    Private Sub CommandButton1_Click()
      sn = Sheet2.Cells(1).CurrentRegion
    
      With Sheet1
        .TextBox2 = "<MAR>" & .TextBox1 & "</MAR>"
        
        For j = 1 To UBound(sn)
          .TextBox2 = Replace(.TextBox2, sn(j, 1), sn(j, 2))
        Next
        .TextBox2 = Replace(Replace(.TextBox2, "#", "; and #"), "; and #", "#", , 1)
      End With
    End Sub
    NB. Put the code in the macromodule it belongs to: sheet1.
    Avoid worksheet interaction (reading / writing) as much as possible: use Arrays (like sn) to do this.

  7. #7
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    613
    Location
    That's neat
    Semper in excretia sumus; solum profundum variat.

  8. #8
    very neat indeed. thanks snb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •