run time error 5

rsl2009

New Member
Joined
Sep 12, 2014
Messages
2
When debugging my codes the red line highlight for an error. Can anyone please help me to solve the issue. Thanks

Code:
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    
    Call Filter_OFF
    
    Range("'[" & ThisWorkbook.Name & "]All Info'!A4:BB60000").Clear
    
    Application.StatusBar = "browsing through " & Server_Path & " to list all CP sheets"
    


Dim i As Long
Dim Recherche As ClFileSearch.ClasseFileSearch
 
 
Set Recherche = ClFileSearch.Nouvelle_Recherche
 
With Recherche
    .FolderPath = Server_Path
    .SubFolders = True
    .SortBy = sort_Name
    .Execute
    
            'Excel_Files_Found = .FoundFilesCount
            
            For i = 1 To .FoundFilesCount
                    full_file_name = .Files(i).strFileName
                    Application.StatusBar = "Reading " & full_file_name
                    
                    If UCase(Right(full_file_name, 3)) <> "XLS" Then GoTo Skip_to_Next_File
                    
                    'This part get the short name of the workbook....
                        
                         suffix_index = Len(full_file_name)
                         Short_File_Name = ""
                        
                        
[COLOR=#ff0000]                        Do While Mid(full_file_name, suffix_index, 1) <> "\"[/COLOR]
                        
                            Short_File_Name = Mid(full_file_name, suffix_index, 1) & Short_File_Name
                            suffix_index = suffix_index - 1
                        Loop
                    
                    Application.EnableEvents = False
                    Workbooks.Open Filename:=full_file_name, ReadOnly:=True
                    
                    'Scan throught all the sheets
                    'Select sheets that contain trims info
                    
                        Sheets_Count = Workbooks(Short_File_Name).Sheets.Count
                        
                        For Sheet_Index = 1 To Sheets_Count
                            Windows(Short_File_Name).Activate
                            
                            Current_Open_CP_Sheet = "'[" & Short_File_Name & "]" & Sheets(Sheet_Index).Name & "'!"
                            'Sheet is a Critical Path
                            If Range(Current_Open_CP_Sheet & "A9") = "by Harry Maulloo, Floreal Knitwear Ltd" _
                            Then
                                
                                
                                'Remove Autofilter to allow reading of all lines
                                Sheets(Sheets(Sheet_Index).Name).Activate
                                
                                ActiveSheet.Unprotect
                                ActiveSheet.AutoFilterMode = False
                                ActiveSheet.Unprotect
                                Range("A16:A65536").EntireRow.Hidden = False
                                
                                
                                If Range("'" & ThisWorkbook.Name & "'!Update_Planning_Dates_and_Calculate").Value _
                                Then
                                    'Force calculation
                                    Application.EnableEvents = False
                                    On Error Resume Next
                                    Application.Run "'" & Short_File_Name & "'!Update_Planning_Dates"
                                    Application.EnableEvents = False
                                    On Error Resume Next
                                    Application.Run "'" & Short_File_Name & "'!Calculate"
                                    On Error GoTo 0
                                End If
                                
                                ThisWorkbook.Activate


                                'Collect Customer, S, M, P info
                                Customer = Range(Current_Open_CP_Sheet & "T8")
                                Sales = Range(Current_Open_CP_Sheet & "AM8")
                                Merchandiser = Range(Current_Open_CP_Sheet & "AM9")
                                Purchaser = Range(Current_Open_CP_Sheet & "AM10")
                                
                                
                                Last_Row = Range(Current_Open_CP_Sheet & "S10000").End(xlUp).Row
                                
                                POS_Column = 4
                                Row_Counter_Start = 16 'This has to be set as a parameter in case of change
                                Trims_Item_Column = 19 'This has to be set as a parameter in case of change
                                
                                For Row_Counter = Row_Counter_Start To Last_Row
                                    
                                    
                                    POS = Range(Current_Open_CP_Sheet & "A19").Offset(Row_Counter - 19, POS_Column - 1)
                                    POS = mod_Global.Trim_and_Format_POS(POS)
                                    
                                    If Not (Loading_POS_Index.Exists(POS)) _
                                    Then GoTo Move_Next_Row
                                    
                                    Trims_Item = Range(Current_Open_CP_Sheet & "A19").Offset(Row_Counter - 19, Trims_Item_Column - 1)
                                    
                                    If Trims_Item <> "" _
                                    Then
                                        'new item line line found --> counter incremented
                                        Record_Counter = Record_Counter + 1
                                        
                                        
                                        'Write Customer,S,M,P Fields
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 0) = Customer
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 1) = Sales
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 2) = Merchandiser
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 3) = Purchaser
                                        
                                        'Write Trims Line
                                        For Column_Index = 1 To 11
                                            
                                            Range("'[" & ThisWorkbook.Name & "]All Info'!E" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
                                                = Range(Current_Open_CP_Sheet & "A16").Offset(Row_Counter - 16, Column_Index - 1)
                                            
                                        Next Column_Index
                                        
                                        
                                        For Column_Index = 1 To 23
                                            
                                            Range("'[" & ThisWorkbook.Name & "]All Info'!P" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
                                                = Range(Current_Open_CP_Sheet & "S16").Offset(Row_Counter - 16, Column_Index - 1)
                                            
                                        Next Column_Index
                                        
                                        For Column_Index = 1 To 6
                                            
                                            Range("'[" & ThisWorkbook.Name & "]All Info'!AM" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
                                                = Range(Current_Open_CP_Sheet & "AQ16").Offset(Row_Counter - 16, Column_Index - 1)
                                            
                                        Next Column_Index
                                        
                                        For Column_Index = 1 To 5
                                            
                                            Range("'[" & ThisWorkbook.Name & "]All Info'!AS" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
                                                = Range(Current_Open_CP_Sheet & "AY16").Offset(Row_Counter - 16, Column_Index - 1)
                                            
                                        Next Column_Index
                                        
                                        
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!AX" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1) _
                                            = Right(full_file_name, Len(full_file_name) - Len(Server_Path))
                                        
                                        Range("AX" & First_Row_In_Compile_Sheet + Record_Counter - 1).Select
                                        ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                        Address:=full_file_name, _
                                        SubAddress:="AR" & Row_Counter
                                                           
                                        Range("'[" & ThisWorkbook.Name & "]All Info'!AX" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1).Font.Size = 8
                                        
                                    End If
                                    
Move_Next_Row:
                                Next Row_Counter
                            End If
                        Next Sheet_Index
                                    
                    ActiveWindow.Close (False)
Skip_to_Next_File:
            Next i
       ' Else
       '     MsgBox "There were no files found."
       ' End If
    End With
    
    Range("Standard_Line").Copy
    Rows("4:" & WorksheetFunction.Max(Record_Counter + 3, 4)).PasteSpecial xlPasteFormats
    Rows(WorksheetFunction.Max(Record_Counter + 3, 4) & ":65536").Clear
    
    Application.StatusBar = "COMPLETED >>> " & Record_Counter & " Accessory Item Processed Successfully!"
    Application.EnableEvents = True
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

What's assigned to full_file_name and suffix_index when you get the error?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top