Search and replace problem

  • Thread starter Thread starter ordnance1
  • Start date Start date
O

ordnance1

Can anyone tell me why my code below only deletes data from worksheet April
and not all of the selected worksheets? I intend that the TextBox1 value be
found on all selected worksheets and be replaced by nothing, in effect
removing the data from all worksheets.



Private Sub CommandButton1_Click()

x = TextBox1.Value

If ActiveSheet.Name = "April" Then

Sheets(Array("April", "May", "June", "July", "August",
"September")).Select

Cells.Replace What:=x, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Sheets("April").Select

End If

Unload UserForm1

End Sub
 
Hi

Replace only work on the active sheet, or while using a sheet reference.
This should do whay you want:

Private Sub CommandButton1_Click()
Dim ShArr
Set ShArr = Sheets(Array("April", "May", "June", "July", "August",
"September"))
x = TextBox1.Value

If ActiveSheet.Name = "April" Then
For Each sh In ShArr
With sh
.Cells.Replace What:=x, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With
Next
End If

Unload UserForm1
End Sub

Regards,
Per
 
ordnance1 said:
Can anyone tell me why my code below only deletes data from worksheet
April
Cells.Replace What:=x, Replacement:="", LookAt:=xlWhole, _
Click to expand...

your 'cells' method is referring to 'active' not 'selected', so that is
allways 'april'
maybe to try:

----
for each ws in Sheets(Array("April", "May", "June", "July", "August",
"September"))
ws.Cells.Replace What:=x, Replacement:="", LookAt:=xlWhole, _

next
----
 
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/showthread.php?t=196113

http://www.thecodecage.com/forumz
Click to expand...


One argument is there in Replace method of Range
Searchwithin:=xlsearchwithinworkbook.
If you add this then the replacement will be for entire workbook.
 
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
 
Back
Top