Getting rid of words in a cell in a Workbook

Posted by Dan on May 25, 2000 12:53 AM

Is there a sexy way to write this macro?

Windows("Test1.xls").Activate
Range("A1").Select

Cells.Find(What:="Sales Account", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Replace What:="Sales Account", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="Sales Account", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Cells.Replace What:="Sales Account", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Range("A1").Select

Windows("Test2.xls").Activate
Range("A1").Select

Cells.Find(What:="Sales Account", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Replace What:="Sales Account", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="Sales Account", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Cells.Replace What:="Sales Account", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Windows("EWS1.xls").Activate
Sheets("Main").Select
Range("A8").Select
End Sub

Posted by Ivan Moala on May 25, 2000 1:54 AM

This is a hard coded version of your macro.
ie. only works on Named sheets ?
Do you want it to work on ALL open workbooks ??

Sub test()
Windows("Test1.xls").Activate
Range("A1").Select
Cells.Replace What:="Sales Account", Replacement:=""

Windows("Test2.xls").Activate
Range("A1").Select
Cells.Replace What:="Sales Account", Replacement:=""

Windows("EWS1.xls").Activate
Sheets("Main").Select
Range("A8").Select
End Sub

Ivan