Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
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? –  PirateEric Jul 17 '12 at 19:14

1 Answer 1

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, _
    ReplaceFormat:=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, _
        ReplaceFormat:=False
  End With
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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