I'm trying to write a VBA macro for SharePoint Designer 2007 that will find/replace text but I cannot figure out the find/replace ojbect like I've been able to do with word. Anyone know how I could search for "cat" and replace it with "cat (needs changed)" and highlighted?

  • 1
    Uh, why would you not just use the find (ctrl+F) and replace (crtl+H) options natively in SPD 2007 or am I missing something? – Eric Alexander Jul 17 '12 at 19:14

It seems you want a find/replace in Excel, not in Sharepoint. Try:

    ThisWorkbook.Sheets("Sheet1").Cells.Replace What:="cat", Replacement:="dog", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

I would use the "ThisWorkBook" object to ensure that the script is being ran in the workbook you are using as opposed to the activeworkbook.

Tips: Use LookAt:=xlPart to find any string with "cat" otherwise use = xlWhole to find the entire cell match. Also, the find doesn't work with filtered information. You would first need to unfilter the information before running the Replace. The "Cells.Replace" can be used in a specific range, so you can use Sheets("Sheet1").Range("A1:B500").Replace instead.

  With ThisWorkbook.Sheets("Sheet1")
    If .FilterMode Then .ShowAllData 'check if filter is on and show all records if on.

    .Range("A1:A500").Replace What:="cat", Replacement:="dog", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  End With

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.