Search and replace problem

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
 
P

Per Jessen

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
 
S

sali

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

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
----
 
J

joel

there are too many things wrong in VBA to attempt an explanation whe
VBA doesn't work the way you expect. In this case, it is just better t
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
 
J

Javed

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


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

ordnance1

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top