Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace all the cells in sheet that include spsific string-via mac
i need to replace all the cells in spesific sheet that include spesific
string with other. i did the following code:(but since there are lot of cells in sheet , it is very very havy. is there a better way? ) Workbooks(Activebook).Sheets("mySheet").Select Cells.Select mystr = "xxx" For Each cell In Selection If (InStr(cell.Value, mystr) = 1) Then cell.Replace What:=mystr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False End If Next cell -- Miri Tz. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace all the cells in sheet that include spsific string-via mac
Miri,
You could limit your code to the used range instead of the entire worksheet. Try this and note that I've added a space either side of your string. The reason for this is if your string was 'add' it would remove the 'add' from 'Haddock'. Sub stantial() Sheets("Sheet1").UsedRange.Select mystr = " xxxx " For Each cell In Selection If InStr(1, cell.Value, mystr, 1) 0 Then cell.Replace What:=mystr, Replacement:="" End If Next cell End Sub Mike "Miri" wrote: i need to replace all the cells in spesific sheet that include spesific string with other. i did the following code:(but since there are lot of cells in sheet , it is very very havy. is there a better way? ) Workbooks(Activebook).Sheets("mySheet").Select Cells.Select mystr = "xxx" For Each cell In Selection If (InStr(cell.Value, mystr) = 1) Then cell.Replace What:=mystr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False End If Next cell -- Miri Tz. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Hyperlink Addresses Help 'Dim OldStr As String, NewStr As String | Excel Programming | |||
How do you include a quotation as part of a string ?? | Excel Programming | |||
How do I add cells to a balance sheet which will include new sum? | Excel Discussion (Misc queries) | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
How can I include a " in my string? | Excel Programming |