If statement in a macro...

cav~firez22 · Aug 23, 2007

Hello everyone,

I have a rather LONG macro, that opens 11 files and copies the data into 1 workbook.

the 11 files are always in the same location, and named the same each day i run this. MY question is, if the file the macro is trying to open, is not there, how can i make it just skip it instead of a debug error.

Here is the intensly long code.

Code:

Sub opencashdepos()

'1 ATLANTA

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\ATL" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("ATL").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("atl").Close
    
'2 JACKSON

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\JCK" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("JCK").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("JCK").Close

'3 SLC

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\SLC" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("SLC").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("SLC").Close

'4 SAC

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\sac" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("sac").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("sac").Close

'5 fif

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\fif" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("fif").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("fif").Close

'6 ver

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\ver" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("ver").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("ver").Close

'7 edm

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\edm" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("edm").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("edm").Close

'8 van

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\van" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("van").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("van").Close
        
'9 tor

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\tor" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("tor").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("tor").Close

'10 hfx

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\hfx" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("hfx").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("hfx").Close
'11 que

Application.ScreenUpdating = False

    ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
    Workbooks.OpenText Filename:= _
        "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\que" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
        Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True

    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:G")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
        On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete

Range("A1:F1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    
    Selection.Copy
    Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
        Sheets("que").Select
    Range("A2").Select
    ActiveSheet.Paste
    
    Application.DisplayAlerts = False
        Windows("que").Close

Application.ScreenUpdating = True

'Call SAVEAScd
'msgbox (Be sure to saveAS ALL AGENCIES Worksheet)

End Sub

If anyone could help with this that would be great. Thanks Much

bruty · Aug 23, 2007

Not sure, but would 'OnError Resume Next' work?

cav~firez22 · Aug 23, 2007

It might.. but im not smart enough to figure out how to write that in yet. Im still in the very early learning stages of writing code.

cav~firez22 · Aug 23, 2007

could anyone help with adding an "onerror resume next"?

please?

Norie · Aug 23, 2007

To see if a file exists use Dir.

Code:

If Dir("O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\ATL")<>"" Then
    ' code for Atlanta
End if

Why are you repeating essentially the same code 11 times?

All that appears to be changing is a filename.

By the way this isn't needed since you use the full path when opening the files.

Code:

ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"

If statement in a macro...

cav~firez22

Well-known Member

Excel Facts

bruty

Active Member

cav~firez22

Well-known Member

cav~firez22

Well-known Member

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

If statement in a macro...

cav~firez22

Well-known Member

Excel Facts

bruty

Active Member

cav~firez22

Well-known Member

cav~firez22

Well-known Member

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock