Yes or No

[Steved]

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

 

[Tom Ogilvy]

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.

 

[Steved]

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





.

 

[Tom Ogilvy]

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