Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA Find & Replace - Escape characters for quotes?

  1. #1
    Join Date
    18th April 2005
    Posts
    40

    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,916

    Re: VBA Find & Replace - Escape characters for quotes?

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

    Roy

    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.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,916

    Re: VBA Find & 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
    VB:
    Worksheets("Sheet1").Cells.Replace _ 
    What:="<table border="5"", Replacement:=""<table border="0""", _ 
    SearchOrder:=xlByColumns, MatchCase:=True 
    
    
    Hope that Helps

    Roy

    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.

  4. #4
    Join Date
    18th April 2005
    Posts
    40

    Re: VBA Find & 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?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: VBA Find & Replace - Escape characters for quotes?

    Hi,

    You just need enough quotes in order for the parser to keep the ones you are interested in
    VB:
    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


  6. #6
    Join Date
    29th April 2014
    Posts
    2

    Re: VBA Find & 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")

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,916

    Re: VBA Find & 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

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: March 28th, 2007, 08:30
  2. Find and replace special characters macro
    By thaj0ka in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2006, 21:20
  3. Escape double quotes?
    By panuvin in forum EXCEL HELP
    Replies: 1
    Last Post: September 2nd, 2005, 16:44

Bookmarks

Posting Permissions

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