Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error handler (excel 2000)

    I am trying to add a error handling part to code, I have it in the code but I think my For loops are messed up. Can anyone see the problem


    Sub errhand()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim I As Long, J As Long, JMax As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B:C").Clear
    strPath = wsh.Range("AC1")
    Set fld = fso.GetFolder(strPath)
    I = 1
    For Each sfl In fld.SubFolders
    wsh.Range("A" & I) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    If strFile = "" Then
    wsh.Range("C" & I) = "No workbook found"
    Else
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    wsh.Range("B" & I) = wbk.Worksheets("Job Summary").Range("C5")
    wsh.Range("C" & I) = wbk.Worksheets("Job Summary").Range("C6")
    wsh.Range("D" & I) = wbk.Worksheets("Job Summary").Range("J8")
    wsh.Range("E" & I) = wbk.Worksheets("Job Summary").Range("J9")
    wsh.Range("F" & I) = wbk.Worksheets("Job Summary").Range("J7")
    wsh.Range("G" & I) = wbk.Worksheets("Job Summary").Range("C29")
    wsh.Range("H" & I) = wbk.Worksheets("Job Summary").Range("C30")
    wsh.Range("J" & I) = wbk.Worksheets("Job Summary").Range("J15")
    wsh.Range("K" & I) = wbk.Worksheets("Job Summary").Range("D26")
    wsh.Range("M" & I) = wbk.Worksheets("Job Summary").Range("D23")
    wsh.Range("N" & I) = wbk.Worksheets("Job Summary").Range("D24")
    JMax = wbk.Worksheets("JobChartsData").Range("F65536").En d(xlUp).Row + 1
    If JMax < 7 Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    Else
    For J = 7 To JMax
    If wbk.Worksheets("JobChartsData").Range("F1").Offset (J, 0).Value > 0 Then
    Range(wbk.Worksheets("JobChartsData").Range("B1"). Offset(J - 3, 0), wbk.Worksheets("JobChartsData").Range("B1").Offset (J - 3, 11)).Copy
    wsh.Paste Destination:=wsh.Range("A1").Offset(I, 15)
    Application.CutCopyMode = False
    Exit For
    End If
    Next J
    If J > JMax Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    End If
    End If
    I = I + 1
    wbk.Close SaveChanges:=False
    Next sfl
    ExitHandler:
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    ThisWorkbook.Close SaveChanges:=True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error handler (excel 2000)

    You're missing an End If. There should be 3 End Ifs above the line I = I + 1:

    If J > JMax Then
    MsgBox "No data in workbook " & strFile & "worksheet JobChartsData"
    End If
    End If
    End If
    I = I + 1

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error handler (excel 2000)

    im getting a error message "invalid procedure call or argument which seems to be occuring at

    ErrHandler:
    MsgBox Err.Description, vbExclamation

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error handler (excel 2000)

    I copied that code from your post and pasted it into a Excel procedure and it works fine for me.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error handler (excel 2000)

    Temporarily disable the error handling by inserting an apostrophe ' in front of the line

    On Error GoTo ErrHandler

    Which line is highlighted now when the error occurs?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error handler (excel 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error handler (excel 2000)

    works, my file path name was hot getting entered properly,

    I ran it through a few files and when the error handler is called, it gets an automation error

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •