End With without With

Fr33dan

New Member
Joined
Jul 18, 2007
Messages
19
I have a marco that usees Application.FileSearch to look for files in multiple locations and checks a csv file to see if see if certain other files match the conditions (the other files always have the same name but always different data). I reworked a couple of if statements to allow for 0 results from the file search but then got an End With without With error:
Code:
Sub Get_URLs()
Dim AgeFiles() As String
Dim AgeNames() As String
Application.ScreenUpdating = False
Put_Dir$ = Range("Put_Path").Address(External:=True)
ModelCode = Range("Model_Code").Value

Sheets("Data_URLs").Cells.ClearContents
Application.ScreenUpdating = False

'--- Need to filter out the non aging files ---

With Application.FileSearch
    lookpath = ""
    progressBar introText:="Preparing Search", percentFinished:=10, totalLength:=30
    If (Range("Cluster_1").Value) Then
        lookpath = lookpath & Range("Cluster_Path").Value & "Cluster1\;"
    End If
    If (Range("Cluster_2").Value) Then
        lookpath = lookpath & Range("Cluster_Path").Value & "Cluster2\;"
    End If
    If (Range("Other").Value) Then
        lookpath = lookpath & Range("Data_Path").Value & ";"
    End If
    If (Range("Model").Value) Then
        lookpath = lookpath & Range("Model_Path").Value & Range("O6").Value
    End If
    
    progressBar introText:="Preparing Search", percentFinished:=20, totalLength:=30
    If lookpath <> "" Then
    .NewSearch
    .LookIn = lookpath
    .SearchSubFolders = True
'----------------------------- look -------------------
    '.FileName = "*." & ModelCode 'W95 stuff
    .FileName = "." & ModelCode 'XP stuff
'    .FileName = "*." & ModelCode 'W95 stuff


    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles
    progressBar introText:="Running Search", percentFinished:=30, totalLength:=30
    If .Execute(SortBy:=msoSortByFileName, _
      SortOrder:=msoSortOrderAscending) > 0 Then
        Range("No_Aging_Files").Value = .FoundFiles.Count
        Offset = 1
        progressBar introText:="Fetching Results", percentFinished:=50, totalLength:=30
        For i = 1 To Range("No_Aging_Files").Value
            If Left$(Right$(.FoundFiles(i), 12), 8) = "COPY_OUT" Or Left$(Right$(.FoundFiles(i), 9), 5) = "REAGE" Then
                Offset = Offset + 1
                Range("No_Aging_Files").Value = Range("No_Aging_Files").Value - 1
            Else
                Range(Put_Dir$).Offset(i - Offset).Value = .FoundFiles(i)
                Range(Put_Dir$).Offset(i - Offset, 2).Value = False
            End If
        Next i
        Offset = i - Offset
        progressBar introText:="Fetching Results", percentFinished:=60, totalLength:=30
    End If
    Count = 0
    If (Range("Unarchived_Files").Value) Then
        Application.ScreenUpdating = False
        Workbooks.Open (Range("Archive_Data_Path").Value & "PULLINFO.CSV")
        i = 1
        While Not IsEmpty(Cells(i, "D"))
            If (Right$(Cells(i, "D"), 3) = ModelCode) Then
                ReDim Preserve AgeFiles(Count + 1)
                ReDim Preserve AgeNames(Count + 1)
                AgeFiles(Count) = Cells(i, "A")
                AgeNames(Count) = Cells(i, "D")
                Count = Count + 1
            End If
            i = i + 1
        Wend
        ActiveWorkbook.Close
        For i = 1 To Count
            Range("No_Aging_Files").Value = Range("No_Aging_Files").Value + 1
            Range(Put_Dir$).Offset(Offset).Value = Range("Archive_Data_Path").Value & AgeFiles(i - 1)
            Range(Put_Dir$).Offset(Offset, 1).Value = AgeNames(i - 1)
            Range(Put_Dir$).Offset(Offset, 2).Value = False
            Offset = Offset + 1
        Next i
    End If
    If .Count <> 0 And Count <> 0 Then
        Range("Current_Aging_File").Value = 1
        progressBar introText:="Loading File", percentFinished:=80, totalLength:=30
        Call Load_and_Graph
    Else
        MsgBox "There were no files found."
        Range("No_Aging_Files").Value = .FoundFiles.Count
        Range("Current_Aging_File").Value = 0
    End If
End With
progressBar introText:="Finished", percentFinished:=100, totalLength:=30
Application.StatusBar = False
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It's actually an End If you appear to be missing.

This compiles, though I had to comment out the calls to progressbar and Load_and_Graph.
Code:
Sub Get_URLs()
Dim AgeFiles() As String
Dim AgeNames() As String
Application.ScreenUpdating = False
Put_Dir$ = Range("Put_Path").Address(External:=True)
ModelCode = Range("Model_Code").Value

Sheets("Data_URLs").Cells.ClearContents
Application.ScreenUpdating = False

'--- Need to filter out the non aging files ---

With Application.FileSearch
    lookpath = ""
    'progressBar introText:="Preparing Search", percentFinished:=10, totalLength:=30
    If (Range("Cluster_1").Value) Then
        lookpath = lookpath & Range("Cluster_Path").Value & "Cluster1\;"
    End If
    If (Range("Cluster_2").Value) Then
        lookpath = lookpath & Range("Cluster_Path").Value & "Cluster2\;"
    End If
    If (Range("Other").Value) Then
        lookpath = lookpath & Range("Data_Path").Value & ";"
    End If
    If (Range("Model").Value) Then
        lookpath = lookpath & Range("Model_Path").Value & Range("O6").Value
    End If
    
    'progressBar introText:="Preparing Search", percentFinished:=20, totalLength:=30
    If lookpath <> "" Then
        .NewSearch
        .LookIn = lookpath
        .SearchSubFolders = True
'----------------------------- look -------------------
    '.FileName = "*." & ModelCode 'W95 stuff
        .Filename = "." & ModelCode 'XP stuff
'    .FileName = "*." & ModelCode 'W95 stuff


        .MatchTextExactly = False
        .FileType = msoFileTypeAllFiles
        'progressBar introText:="Running Search", percentFinished:=30, totalLength:=30
        If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then
            Range("No_Aging_Files").Value = .FoundFiles.Count
            Offset = 1
            'progressBar introText:="Fetching Results", percentFinished:=50, totalLength:=30
            For i = 1 To Range("No_Aging_Files").Value
                If Left$(Right$(.FoundFiles(i), 12), 8) = "COPY_OUT" Or Left$(Right$(.FoundFiles(i), 9), 5) = "REAGE" Then
                    Offset = Offset + 1
                    Range("No_Aging_Files").Value = Range("No_Aging_Files").Value - 1
                Else
                    Range(Put_Dir$).Offset(i - Offset).Value = .FoundFiles(i)
                    Range(Put_Dir$).Offset(i - Offset, 2).Value = False
                End If
            Next i
            Offset = i - Offset
        '  progressBar introText:="Fetching Results", percentFinished:=60, totalLength:=30
        End If
        Count = 0
        If (Range("Unarchived_Files").Value) Then
            Application.ScreenUpdating = False
            Workbooks.Open (Range("Archive_Data_Path").Value & "PULLINFO.CSV")
            i = 1
            While Not IsEmpty(Cells(i, "D"))
                If (Right$(Cells(i, "D"), 3) = ModelCode) Then
                    ReDim Preserve AgeFiles(Count + 1)
                    ReDim Preserve AgeNames(Count + 1)
                    AgeFiles(Count) = Cells(i, "A")
                    AgeNames(Count) = Cells(i, "D")
                    Count = Count + 1
                End If
                i = i + 1
            Wend
            ActiveWorkbook.Close
            For i = 1 To Count
                Range("No_Aging_Files").Value = Range("No_Aging_Files").Value + 1
                Range(Put_Dir$).Offset(Offset).Value = Range("Archive_Data_Path").Value & AgeFiles(i - 1)
                Range(Put_Dir$).Offset(Offset, 1).Value = AgeNames(i - 1)
                Range(Put_Dir$).Offset(Offset, 2).Value = False
                Offset = Offset + 1
            Next i
        End If
        If .Count <> 0 And Count <> 0 Then
            Range("Current_Aging_File").Value = 1
            ' progressBar introText:="Loading File", percentFinished:=80, totalLength:=30
            'Call Load_and_Graph
        Else
            MsgBox "There were no files found."
            Range("No_Aging_Files").Value = .FoundFiles.Count
            Range("Current_Aging_File").Value = 0
        End If
    End If
End With
'progressBar introText:="Finished", percentFinished:=100, totalLength:=30
Application.StatusBar = False
End Sub
 
Upvote 0
That was it I knew it could be some other end statement missing but I couldn't figure out what was missing. I just added another end if after the end if for If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 and it worked beautifully. Thanks for your help

Edit: Posted this before I saw Norie's post. Thanks Norie but putting the if statement there makes all the code run if lookpath is empty I just don't want the if filesearch to run if it's empty.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top