Announcement

Collapse
No announcement yet.

VBA Find & Replace ?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: VBA Find & Replace ?

    see if this code helps

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

    Comment


    • #3
      Re: VBA Find & Replace ?

      Nevermind, I think I found it..

      Code:
      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, 15:27.

      Comment


      • #4


        Re: VBA Find & Replace ?

        Hi essential_clix

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

        Comment

        Working...
        X