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

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

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

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

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

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

.

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