Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
Hi Steved,
For every If that you see in your code there must be an End If, i hav just had a quick scan of your code and noticed you have somethin like:- If Not bFirst Then ans = MsgBox("Go again", vbYesNo) If ans = vbNo Then Exit Sub End If instead of your second If ans= blah blah you should try ElseIf ans=bla blah HTH Simo -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
Steved
You have left out an Enf If for the last If just before the End Sub Neil "Steved" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
Steved,
This is a somewhat misleading error message, in fact a very common one in VBA! While it has you looking for an If without an End If, the real problem is that you have a Do without a Loop; the second Do loop starting in line 9 after the Dim's is left 'open', without a Loop statement to close it. HTH, Nikos "Steved" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
It looks like the code got screwed up somehow. Anyway, this compiles for
me: 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 End If 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 -- Regards, Tom Ogilvy "Steved" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
Thankyou all
I actually tried for a couple of hours myself but kept getting a compile error. So I Thankyou. Cheers -----Original Message----- 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please tell me
I've noticed this compiler error too.
John Nikos Yannacopoulos wrote: Steved, This is a somewhat misleading error message, in fact a very common one in VBA! While it has you looking for an If without an End If, the real problem is that you have a Do without a Loop; the second Do loop starting in line 9 after the Dim's is left 'open', without a Loop statement to close it. HTH, Nikos "Steved" wrote in message ... 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 -- R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s Change LID to |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|