Announcement

Collapse
No announcement yet.

VBA Find & Replace - Escape characters for quotes?

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Find & Replace - Escape characters for quotes?

    Hi guys,

    I'm trying to use a macro to search, find & replace some HTML throughout 500+ cells using the following code:

    Code:
    Sub ReplaceText6()
        Dim a As Range
        For Each a In ActiveSheet.UsedRange
            a = Replace(a, "<table border="5"", "<table border="0"")
        Next
    End Sub
    It gives me the compile error. What are the escape characters for VBA in this situation? Am I even going about this the right way?

    Thanks and kindest regards,
    EC

  • #2
    Re: VBA Find &amp; Replace - Escape characters for quotes?

    Try deleting the a, out of the brackest
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: VBA Find &amp; Replace - Escape characters for quotes?

      I don't think Excel likes the string you are looking for. Can't you edit the HTML in FrontPage?

      You should use something like this, but the speech marks seem to confuse the Method
      Code:
      Worksheets("Sheet1").Cells.Replace _
          What:="<table border="5"", Replacement:=""<table border="0""", _
          SearchOrder:=xlByColumns, MatchCase:=True
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: VBA Find &amp; Replace - Escape characters for quotes?

        Hi, I've tried removing the a from the brackets, with no avail. I'm not quite sure how to use/implement the code in your second post though.

        I could use an html editor, but theres 500+ cells that all contain the same code that needs to be replaced, so it would take a very long time and would be incredibly mundane. I know how to export the cell data (html) into individual .html files, so I could use an html editor that way, but then these files would have to somehow be imported back into the cells they originated form. I have no idea how to accomplish that...

        Any other ideas/suggestions?

        Comment


        • #5
          Re: VBA Find &amp; Replace - Escape characters for quotes?

          Hi,

          You just need enough quotes in order for the parser to keep the ones you are interested in
          Sub ReplaceText6()
          Dim a As Range
          For Each a In ActiveSheet.UsedRange
          a = Replace(a, "<table border=""5""", "<table border=""0""")
          Next

          End Sub

          Cheers
          Andy

          Comment


          • #6
            Re: VBA Find &amp; Replace - Escape characters for quotes?

            I am also having the similar problem.
            I want to replace the " symbol
            and want to use Replace func
            Text:
            " insert the table
            want to remove the " with space(or nothing) through this function Can i do it?
            a = Replace(CellData, " " insert", "insert")

            Comment


            • #7
              Re: VBA Find &amp; Replace - Escape characters for quotes?

              Welcome to the Forum

              Please do not ask your questions in another member's Thread. Start your own and if you think it will help add a link to this post.
              Hope that Helps

              Roy

              New users should read the Forum Rules before posting

              For free Excel tools & articles visit my web site

              If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

              RoyUK's Web Site

              royUK's Database Form

              Where to paste code from the Forum

              About me.

              Comment

              Working...
              X