Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
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" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
"ordnance1" je napisao u poruci interesnoj
... 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 ---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace problem
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search and replace | Excel Discussion (Misc queries) | |||
Search and replace | Excel Worksheet Functions | |||
Search and Replace Problem | Excel Programming | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) | |||
Search and Replace | New Users to Excel |