Search and replace problem
in [Programming]
|
From: ordnance1 on 20 Apr 2010 16:27 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" <asadullajaved(a)gmail.com> wrote in message news:73c4793e-2ebc-4645-bbcb-d981d6d49e6e(a)w32g2000prc.googlegroups.com... > 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. |