Count

[Steved]

Hello from Steved

I would like to please to take the below and add a count
to it please, meaning how many times did it do replacing,
as at the moment I have to check each worksheet. I know
for example how many it should do for example I put the
value 2202 and to replace it with value 4403, it will
find in all open workbooks 2202 in 8 different workbooks
and replace it with 4403, What I need to do is now check
each of these workbooks, if their could be a total of 8
in the input box I know it is right and that would save
me checking it.

Thankyou.


Sub ChangeAllValues2()
Dim mySht As Worksheet
Dim myBook As Workbook
Dim ReplaceWith As String
Dim ToReplace As String

ToReplace = Application.InputBox("What value to replace?")
ReplaceWith = Application.InputBox("Replace '" & _
ToReplace & "' with what other value?")

For Each myBook In Application.Workbooks
For Each mySht In myBook.Worksheets
mySht.Cells.Replace _
ToReplace, ReplaceWith, _
xlWhole
Next mySht
Next myBook

End Sub

 

[Tom Ogilvy]

I put in some lines which you may want to use or tailor them to produce the
information you need.

Sub ChangeAllValues2()
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

cnt = 0
ToReplace = Application.InputBox("What value to replace?")
ReplaceWith = Application.InputBox("Replace '" & _
ToReplace & "' with what other value?")

For Each myBook In Application.Workbooks
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
Next myBook
msgbox cnt & " sheets were changed"
End Sub

 

[Steved]

Thankyou Tom.

-----Original Message-----
I put in some lines which you may want to use or tailor them to produce the
information you need.

Sub ChangeAllValues2()
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

cnt = 0
ToReplace = Application.InputBox("What value to replace?")
ReplaceWith = Application.InputBox("Replace '" & _
ToReplace & "' with what other value?")

For Each myBook In Application.Workbooks
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
Next myBook
msgbox cnt & " sheets were changed"
End Sub

--
Regards,
Tom Ogilvy





.