Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes or No
Hello from Steved
Below allows for Find and Replace I would like a modification please The modification would when found, "yes to replace or ignore". Thankyou. Sub ProcessBooks() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim mySht As Worksheet Dim myBook As Workbook Dim ReplaceWith As String Dim ToReplace As String Dim cnt As Long, num As Long, num1 As Long Dim ans As Variant Dim bFirst As Boolean ChDrive "M:" ChDir "M:\a-tt\a-work'g\mon-fri" FName = Dir("*.xls") Do Until FName = "" Set WB = Workbooks.Open(FName) FName = Dir() Loop bFirst = True Do While True cnt = 0 If Not bFirst Then ans = MsgBox("Go again", vbYesNo) If ans = vbNo Then Exit Sub End If bFirst = False ToReplace = Application.InputBox("What value to replace?") ReplaceWith = Application.InputBox("Replace '" & _ ToReplace & "' with what other value?") If ToReplace = "" Then Exit Do For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then For Each mySht In myBook.Worksheets num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If Next mySht End If Next myBook MsgBox cnt & " sheets were changed" Loop For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then myBook.Close SaveChanges:=True End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes or No
If you want to OK it for each sheet
For Each mySht In myBook.Worksheets if msgbox("OK to replace",vbYesNo) = vbYes then num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If End if ' OK to replace Next mySht If you need to OK each cell, then you will need to redesign this part of your code to be similar to the other code you were working on using the Find method. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below allows for Find and Replace I would like a modification please The modification would when found, "yes to replace or ignore". Thankyou. Sub ProcessBooks() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim mySht As Worksheet Dim myBook As Workbook Dim ReplaceWith As String Dim ToReplace As String Dim cnt As Long, num As Long, num1 As Long Dim ans As Variant Dim bFirst As Boolean ChDrive "M:" ChDir "M:\a-tt\a-work'g\mon-fri" FName = Dir("*.xls") Do Until FName = "" Set WB = Workbooks.Open(FName) FName = Dir() Loop bFirst = True Do While True cnt = 0 If Not bFirst Then ans = MsgBox("Go again", vbYesNo) If ans = vbNo Then Exit Sub End If bFirst = False ToReplace = Application.InputBox("What value to replace?") ReplaceWith = Application.InputBox("Replace '" & _ ToReplace & "' with what other value?") If ToReplace = "" Then Exit Do For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then For Each mySht In myBook.Worksheets num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If Next mySht End If Next myBook MsgBox cnt & " sheets were changed" Loop For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then myBook.Close SaveChanges:=True End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes or No
Thankyou very much Tom.
-----Original Message----- If you want to OK it for each sheet For Each mySht In myBook.Worksheets if msgbox("OK to replace",vbYesNo) = vbYes then num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If End if ' OK to replace Next mySht If you need to OK each cell, then you will need to redesign this part of your code to be similar to the other code you were working on using the Find method. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below allows for Find and Replace I would like a modification please The modification would when found, "yes to replace or ignore". Thankyou. Sub ProcessBooks() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim mySht As Worksheet Dim myBook As Workbook Dim ReplaceWith As String Dim ToReplace As String Dim cnt As Long, num As Long, num1 As Long Dim ans As Variant Dim bFirst As Boolean ChDrive "M:" ChDir "M:\a-tt\a-work'g\mon-fri" FName = Dir("*.xls") Do Until FName = "" Set WB = Workbooks.Open(FName) FName = Dir() Loop bFirst = True Do While True cnt = 0 If Not bFirst Then ans = MsgBox("Go again", vbYesNo) If ans = vbNo Then Exit Sub End If bFirst = False ToReplace = Application.InputBox("What value to replace?") ReplaceWith = Application.InputBox("Replace '" & _ ToReplace & "' with what other value?") If ToReplace = "" Then Exit Do For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then For Each mySht In myBook.Worksheets num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If Next mySht End If Next myBook MsgBox cnt & " sheets were changed" Loop For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then myBook.Close SaveChanges:=True End If Next End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes or No
since you aren't activating sheets:
For Each mySht In myBook.Worksheets msg = " in Book: " & myBook.name & " Sheet: " & mySht.name if msgbox("OK to replace" & msg,vbYesNo) = vbYes then -- Regards, Tom Ogilvy "Steved" wrote in message ... Thankyou very much Tom. -----Original Message----- If you want to OK it for each sheet For Each mySht In myBook.Worksheets if msgbox("OK to replace",vbYesNo) = vbYes then num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If End if ' OK to replace Next mySht If you need to OK each cell, then you will need to redesign this part of your code to be similar to the other code you were working on using the Find method. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below allows for Find and Replace I would like a modification please The modification would when found, "yes to replace or ignore". Thankyou. Sub ProcessBooks() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim mySht As Worksheet Dim myBook As Workbook Dim ReplaceWith As String Dim ToReplace As String Dim cnt As Long, num As Long, num1 As Long Dim ans As Variant Dim bFirst As Boolean ChDrive "M:" ChDir "M:\a-tt\a-work'g\mon-fri" FName = Dir("*.xls") Do Until FName = "" Set WB = Workbooks.Open(FName) FName = Dir() Loop bFirst = True Do While True cnt = 0 If Not bFirst Then ans = MsgBox("Go again", vbYesNo) If ans = vbNo Then Exit Sub End If bFirst = False ToReplace = Application.InputBox("What value to replace?") ReplaceWith = Application.InputBox("Replace '" & _ ToReplace & "' with what other value?") If ToReplace = "" Then Exit Do For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then For Each mySht In myBook.Worksheets num = Application.CountIf(mySht.UsedRange, ToReplace) mySht.Cells.Replace _ ToReplace, ReplaceWith, _ xlWhole num1 = Application.CountIf(mySht.UsedRange, ToReplace) If num 0 Then cnt = cnt + 1 End If If num1 < 0 And num 0 Then MsgBox "Problems with " & mySht.Name End If Next mySht End If Next myBook MsgBox cnt & " sheets were changed" Loop For Each myBook In Application.Workbooks If myBook.Name < ThisWorkbook.Name Then myBook.Close SaveChanges:=True End If Next End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|