If...Then...Else Loop Error

[rbrizz11]

I am trying to write code for a If..Then..Else loop which will loop through 3 different ranges of check box's (TRUE) which if 1st range is true (open workbook) and run macro1, if 2nd range true (activeworkbook) run macro2...etc, however when I have a space in between rows in my first column, the entire application closes and stops. I think I might be missing a next statement somewhere, or an else statement. Any Ideas?

[VBA]Sub INVOICES_HEALTH()
Dim wb As Workbook
Dim ws As Worksheet
Dim TheFile As String
Dim MyPath As String
Dim MySAVEAS As String
Dim MyHEALTH As Range
Dim x As Integer
Dim iCell As Range
Dim MyName As Name



MySAVEAS = "S:\Corp\NewFinance\National Accounts\6 SELF FUNDED BILLING\2 Non Imported1"
MyPath = "S:\Corp\NewFinance\National Accounts\6 SELF FUNDED BILLING\2 Weekly Invoices"
Control = "S:\Corp\NewFinance\National Accounts\6 SELF FUNDED BILLING\1 CONTROL SHEET"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set MyName = ActiveWorkbook.Name


For Each iCell In Range("MyHEALTH")
If iCell.Value Then
Set wb = Workbooks.Open(Filename:=MyPath & "\" & iCell.Offset(0, -1).Value _
, UpdateLinks:=3)
Application.Run "'" & TheFile & "'!Health"
End If

If iCell.Offset(0, 1).Value Then
Application.Run "'" & TheFile & "'!DRUG"
End If

If iCell.Offset(0, 2).Value Then
Application.Run "'" & TheFile & "'!CAP"
End If


For Each ws In ActiveWorkbook.Worksheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
Next


ActiveWorkbook.SaveAs Filename:=MySAVEAS & "\" & TheFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Next iCell



End Sub
[/VBA]

http://www.excelforum.com/excel-prog...oop-error.html

[pitchie]

It would appear to me that this declaration:

[VBA]Dim TheFile As String [/VBA]

Is Null or Nothing when called at:

[VBA]
Application.Run "'" & TheFile & "'!Health"
Application.Run "'" & TheFile & "'!DRUG"
Application.Run "'" & TheFile & "'!CAP"
[/VBA]

I can't test your code here but it would appear to me that "TheFile" appears to be some sort of path to a file. However, I cannot see where it ever gets declared in your code.

HTH!

Paul.