Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello from Steved
I am a beginner in programming I am getting a compile error: Block IF without EndIf What is needed for the below please. 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 "C:" ChDir "C:\Wtt" 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 msg = " in Book: " & myBook.Name & " Sheet: " & mySht.Name If MsgBox("OK to replace" & msg, 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 mySht myBook MsgBox cnt & " sheets were changed" myBook.Close SaveChanges:=True End Sub |