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?
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