Search and replace problem
in [Programming]
|
From: ordnance1 on 16 Apr 2010 05:17 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
From: Per Jessen on 16 Apr 2010 05:33 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" <ordnance1(a)comcast.net> skrev i meddelelsen news:E7ADA923-C7E3-40C9-A62E-32DF24A436BE(a)microsoft.com... > 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
From: sali on 16 Apr 2010 05:42 "ordnance1" <ordnance1(a)comcast.net> je napisao u poruci interesnoj grupi:E7ADA923-C7E3-40C9-A62E-32DF24A436BE(a)microsoft.com... > 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 ----
From: joel on 16 Apr 2010 06:35 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
From: Javed on 20 Apr 2010 04:56
On Apr 16, 3:35 pm, joel <joel.49i...(a)thecodecage.com> 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/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. |