Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count
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 "Steved" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count
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 "Steved" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |