Solved: IF...ELSE...THEN LOOP HELP Please Help!

[rbrizz11]

I am having issues with the code for my if...then... else loop:

Basically, I have a range named "MyRange" in my worksheet (column B1:B10) which contains the True/False from a checkbox in column A1:A10. In column C1:C10 I have text names (ex Schedule) which is the name of the file in another file path (I have the path to where the files I want opened named MyPath = ....). What I would like to write is something like:

If MyRange is True
Then Open wb MyRange.Offset(0,1).Value that is located in MyPath & "/" & "xls"
Else move on to the next row in the True/False column

Can anyone please help??

[Kenneth Hobs]

Welcome to the forum!

Perhaps something like:
[VBA] Dim cel As Range, s As String
Dim MyPath As String
'Dim MyRange As Range
'MyPath = ThisWorkbook.Path
'Set MyRange = Range("B1", Range("B" & Rows.Count).End(xlUp))

For Each cel In Range("MyRange")
s = MyPath & Application.PathSeparator & Range("C" & cel.Row).Value2 & ".xls"
If Dir(s) <> "" And cel.Value = True Then
Workbooks.Open (s)
Exit For
End If
Next cel[/VBA]

[Bob Phillips]

[vba]

For Each cell in Range("MyRange")

If cell.Value Then

Workbooks.Open cell.Offset(0, 1).Value2
Exit For
End If
Next cell
[/vba]

[rbrizz11]

These both worked great, thank you... if I would like to run a macro within each file that I have been opened, would I add the code within the end if? below is the code that i have for running the macro within each file and saving as into another directory.. thanks again

[VBA]
MySAVEAS = "S:\Corp\NewFinance\National Accounts\6 SELF FUNDED BILLING\2 Non Imported"
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

For Each iCell In Range("MyRange")
If LCase(iCell.Value) = "true" Or iCell.Value = 1 Then
Workbooks.Open Filename:=MyPath & "\" & iCell.Offset(0, 1).Value & ".xls" _
, UpdateLinks:=3
End If
Next iCell

Application.Run "'" & current_file_name & "'!Health"

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

ChDir MyPath
TheFile = Dir("*.xls")

ActiveWorkbook.SaveAs Filename:=MySAVEAS & "\" & TheFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
[/VBA]

[Bob Phillips]

Put it in a separate procedure and call that procedure before the Exit For.

[rbrizz11]

When I run this, it says "Runtime error 1004" "the macro 'file.xls!health' cannot be found"

any advice?

[VBA]For Each cell In Range("MyRange")

If cell.Value Then

Workbooks.Open cell.Offset(0, 1).Value2 _
, UpdateLinks:=3
Run ActiveWorkbook.Name & "!Health"

Exit For
End If
Next cell[/VBA]

[rbrizz11]

[vba]
Sub INVOICEScheck()
Dim wb As Workbook
Dim ws As Worksheet
Dim TheFile As String
Dim MyPath As String
Dim MySAVEAS As String
Dim MyRange As Range

MySAVEAS = "S:\Corp\NewFinance\National Accounts\6 SELF FUNDED BILLING\2 Non Imported"
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


For Each cell In Range("MyRange")

If cell.Value Then

Workbooks.Open cell.Offset(0, 1).Value2 _
, UpdateLinks:=3


Run ActiveWorkbook.Name & "!Health"

Exit For
End If
Next cell


For Each ws In ActiveWorkbook.Worksheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select


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


End Sub
[/vba]

[Bob Phillips]

Is the macro in Activeworkbook or ThisWorkbook? If really in Activeworkbook, use

[vba]Run ActiveWorkbook.Name & "!Health" [/vba]

else

use

[vba]Call Health[/vba]

[rbrizz11]

Got it to work great... thank you very much for your help