Vba Loop to Edit Excel files fails on Second Iteration

Question

I am trying to build a routine that opens each excel file in a folder, searches ( using the Find command) for the string “# Issues”, selects the entire row where it finds the string, then delete that row and the next two additional rows. It runs successfully the first iteration, but fails the second time through the loop st the Find command.

I have struggled with this failure on second iteration several times with different code, making me think I am making some kind of mistake in defining my objects.

My code:

Function CleanFilesInAGivenFolder(strFolder As String, _
         strCellLoc As String, _
         strNewValue As String)

Dim strReportType As String
Dim myfile
Dim mypath
Dim strPathFileName As String

If Right(strFolder, 1) <> "\" Then
   strFolder = strFolder & "\"
End If
mypath = strFolder
ChDir (strFolder)

myfile = Dir(mypath)
ChDir (mypath)
myfile = Dir("")

Do While myfile <> ""

        'Format the excel report
            strPathFileName = mypath & myfile

            Dim appExcel As Excel.Application
            Dim wkb As Excel.Workbook
            Dim sht As Worksheet
            Dim rng As Range
            Dim FoundCell As Range

          'Define the file and location
            strPathFileName = mypath & myfile

           'define the excel session
            Set appExcel = New Excel.Application
            appExcel.Visible = True

           'Define the workbook
            Set wkb = appExcel.Workbooks.Open(strPathFileName)
                          'Make sheet 1 the active sheet   
                          Set wkb = ActiveWorkbook
           Set sht = ActiveSheet


           'Find the row with "# Issues" in column A, 
           'delete row this next 2 rows
           'Only works the first iteration of loop


    With sht
         Set FoundCell = Selection.Find(What:="# Issues", _ 
         After:=[a1], 
         LookIn:=xlValues, _
         LookAt:=xlPart, _
         SearchOrder:=xlByRows, _
         SearchDirection:=xlNext, _
         MatchCase:=False, _
         SearchFormat:=False) 
         ActiveCell.EntireRow.Select
     End With


If Not FoundCell Is Nothing Then
    Selection.Delete Shift:=xlUp
    Selection.Delete Shift:=xlUp
    Selection.Delete Shift:=xlUp
End If
          'Clear Objects
            appExcel.DisplayAlerts = False
            wkb.Save
            wkb.Close
            appExcel.DisplayAlerts = True
            Set rng = Nothing
            Set sht = Nothing
            Set wkb = Nothing
            appExcel.Quit
            Set appExcel = Nothing

     myfile = Dir()

Loop
End Function

Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/6535336). From what I can see in your code, you are doing the `Find` within `Selection`, but you probably want to do a `sht.Columns(1).Find` instead. But then you go on to delete things based on `ActiveCell`, but you probably want to do the deletion based on `FoundCell` (possibly `FoundCell.Resize(3, 1).EntireRow.Delete Shift:=xlUp`, but I'm not sure). — YowE3K, Dec 04 '17 at 20:47
You might be causing problems with how you set the workbook object. I don't think you should set it to the `ActiveWorkbook` at all, especially after you just opened the target workbook. I would try to avoid using `Active` and `Select` altogether. Use the `.Address` property of `FoundCell` to get the row to delete, and delete by referencing the row number, not by selecting it. — MoondogsMaDawg, Dec 04 '17 at 20:48
@MoondogsMaDawg Thanks for taking a look. I reviewed the post about using Select. I removed the ActiveWorkbook and Activesheet set statements, and removed the .select on the Find command. It is still failing on the 2nd iteration at the Find command. — lclambe, Dec 04 '17 at 21:17
I'm not sure what I would replace the "Set FoundCell = " with. — lclambe, Dec 04 '17 at 21:53
Sorry, no I'm saying you didn't clean up the variable object. When you set `rng`, `sht`, `wkb` to Nothing, also set `FoundCell` to Nothing. I don't think that's the problem, but it could rule out an inconsistent state. And it's not clear which `Selects` you changed, but make sure to just use `.Find` instead of `Selection.Find` in the `With sht` block. — MoondogsMaDawg, Dec 04 '17 at 22:04

score 0 · Answer 1 · answered Dec 04 '17 at 22:26

Keeping in mind some of the comments from above:

Sub CleanFilesInAGivenFolder(strFolder As String)
    Dim FoundCell As Range
    Dim myFile As String
    Dim wkb As Workbook
    If Right(strFolder, 1) <> "\" Then
        strFolder = strFolder & "\"
    End If
    myFile = Dir(strFolder & "*.xlsx")
    Do While myFile <> ""
        Set wkb = Workbooks.Open(myFile)
        Do
            With Worksheets(1)
                Set FoundCell = .Cells.Find(What:="# Issues", _
                    After:=Range("A1"), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
            End With
            If Not FoundCell Is Nothing Then
                FoundCell.EntireRow.Resize(3).Delete
            Else
                Exit Do
            End If
        Loop
        wkb.Save
        wkb.Close
        myFile = Dir()
    Loop
End Sub

score 0 · Answer 2 · answered Dec 04 '17 at 22:44

Here is the working code. I removed the select and active statements, and replaced the Selection.Find in the Find statement with .Cells.Find

Function CleanFilesInAGivenFolder(strFolder As String, _
         strCellLoc As String, strNewValue As String)

Dim strReportType As String
Dim strCell As String
Dim strValue As String
Dim myfile
Dim mypath
Dim strPathFileName As String

strCell = strCellLoc
strValue = strNewValue

'if it needs a backslash on the end, add one
If Right(strFolder, 1) <> "\" Then
   strFolder = strFolder & "\"
End If

mypath = strFolder
ChDir (strFolder)

myfile = Dir(mypath)
ChDir (mypath)
myfile = Dir("")


Do While myfile <> ""

        'Format the excel report
            strPathFileName = mypath & myfile

            Dim appExcel As Excel.Application
            Dim wkb As Excel.Workbook
            Dim sht As Worksheet
            Dim rng As Range
            Dim FoundCell As Range

          'Define the file and location
            strPathFileName = mypath & myfile

           'define the excel session
            Set appExcel = New Excel.Application
            appExcel.Visible = True

           'Define the workbook
            Set wkb = appExcel.Workbooks.Open(strPathFileName)

Set sht = wkb.Sheets(1)
  With sht
     Set FoundCell = .Cells.Find(What:="# Issues", _
     After:=.Cells(1, 1), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False)

    If Not FoundCell Is Nothing Then
        FoundCell.Offset(2, 0).Delete Shift:=xlUp
        FoundCell.Offset(1, 0).Delete Shift:=xlUp
        FoundCell.Delete Shift:=xlUp
    End If
 End With
          'Clear Objects
            appExcel.DisplayAlerts = False
            wkb.Save
            wkb.Close
            appExcel.DisplayAlerts = True
            Set rng = Nothing
            Set sht = Nothing
            Set wkb = Nothing
            Set FoundCell = Nothing
            appExcel.Quit
            Set appExcel = Nothing
        myfile = Dir()
   Loop
   MsgBox "The Excel File Edits Are Completed", vbExclamation
End Function

@moondogsMaDawg - thanks for steering me in the right direction. — lclambe, Dec 04 '17 at 22:50

Vba Loop to Edit Excel files fails on Second Iteration

2 Answers2