Thanks or your reply.

I have to avoid replacing through the entire workbook. This is a vacation
calendar which combines 3 workgroups and for record keeping I can not remove
a name if the employee took vacation in prior months (even if the employee
is now gone). I now have a series of If statements that seems to do the
trick.

If ActiveSheet.Name = "March" Then

Set ShArr = Sheets(Array("March", "April", "May", "June", "July", "August",
"September", "October", "November", "December"))
x = TextBox1.Value

For Each Sh In ShArr
With Sh
.Cells.Replace What:=x, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range("A3").Activate
End With
Next
Sheets("March").Select
End If



"Javed" wrote in message
...
On Apr 16, 3:35 pm, joel wrote:
there are too many things wrong in VBA to attempt an explanation when
VBA doesn't work the way you expect. In this case, it is just better to
use good programming style and change the code as follows

Private Sub CommandButton1_Click()

ShtNames = Array("April","June","July","August","September")

x = TextBox1.Value

for each shtname in ShtNames
Set Sht = sheets(shtname)

Sht.Cells.Replace What:=x, Replacement:="", _
LookAt:=xlWhole, _
ReplaceFormat:=False
next sht

Unload UserForm1

End Sub

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229
View this
thread:http://www.thecodecage.com/forumz/sh...d.php?t=196113

http://www.thecodecage.com/forumz


One argument is there in Replace method of Range
Searchwithin:=xlsearchwithinworkbook.
If you add this then the replacement will be for entire workbook.