rbrizz11
05-03-2011, 12:38 PM
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?
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
http://www.excelforum.com/excel-programming/774614-if-then-else-loop-error.html
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
http://www.excelforum.com/excel-programming/774614-if-then-else-loop-error.html