Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: VBA Find & Replace ?

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

    VBA Find & Replace ?

    Hi guys/gals,

    Does anyone know of some generic VBA code that will perform a find/replace? I need to search through about 500+ cells (all in one column) for a string of text and replace it with another string of text. Each cell contains about 12,000+ characters (with spaces), so the contents of each cell are rather large.

    I've tried using the basic find/replace command via the edit menu, but it gives me the error msg: Formula is too long. Which is why I was hoping one of you might know some VBA code to fix this

    If so, let's just say that it's column D that I'm working on and the text string I want to replace is "Happy Birthday" and replace it with "Merry Christmas".

    If you have spare time, PLEASE HELP!

    Thanks so much and kindest regards,
    Essential_clix

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th July 2004
    Posts
    1,370

    Re: VBA Find & Replace ?

    see if this code helps

    VB:
    Sub findrep() 
        Dim target, cell As Range 
        Dim i, k As String 
        i = "Happy Birthday" 
        k = "Merry Christmas" 
        Set target = Sheets("sheet1").Range(Range("D1"), Range("D65536").End(xlUp)) 
        For Each cell In target 
            If cell.Value = i Then cell.Value = k 
        Next cell 
    End Sub 
    
    
    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: VBA Find & Replace ?

    Nevermind, I think I found it..

    VB:
    Sub ReplaceText() 
        Dim c As Range 
        For Each c In ActiveSheet.UsedRange 
            c = Replace(c, "TextToFind", "TextToReplace") 
        Next 
    End Sub 
    
    
    Seems to be working good so far =)

    Love this site!
    Last edited by Dave Hawley; October 6th, 2005 at 16:27.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: VBA Find & Replace ?

    Hi essential_clix

    Please read THIS PAGE IN FULL.I have added the code tags for you this time.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Find And Replace *
    By Malhar in forum EXCEL HELP
    Replies: 3
    Last Post: September 15th, 2006, 06:48
  2. find and replace
    By dogmanosu in forum EXCEL HELP
    Replies: 10
    Last Post: January 19th, 2006, 23:48
  3. Find and Replace
    By koba in forum EXCEL HELP
    Replies: 3
    Last Post: November 24th, 2005, 10:08
  4. Find and Replace
    By Ranger in forum EXCEL HELP
    Replies: 3
    Last Post: July 12th, 2004, 03:20
  5. VBA: FInd and Replace
    By Bazman in forum EXCEL HELP
    Replies: 1
    Last Post: August 30th, 2003, 02:25

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