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
-
1Uh, 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– Eric Alexander2012-07-17 19:14:13 +00:00Commented Jul 17, 2012 at 19:14
Add a comment
|
1 Answer
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
