Поиск строки VBA в файлах в каталоге не работает в Excel 2007

Question

Я пытаюсь найти строку в папке, содержащей несколько файлов. Как только строка найдена, она должна выбрать и скопировать значение в соседней ячейке на 5 столбцов справа (Offset(0,5)) , а затем вставить его в мою книгу. Если найдено больше строк, они должны быть вставлены как конец списка (xlDown).

Это идея, но я не могу заставить ее работать, сообщение об ошибке: « Нижний индекс вне диапазона ».

Sub searchBOM()

Dim BOM As String 'the text i'm gonna look for
Dim path As String 'the folder containing several files
Dim filename As String 'one of the files to search in

path = "D:\folder\"
filename = Dir(path)
BOM = InputBox("please enter bom code") 'where the user enters the string

If BOM = "" Then
        MsgBox ("please input valid BOM code") 'not important really, just a small validation
   Else
        Do While filename <> "" 'so the DIR function scans all files
              **'here I get the error message SUBSCRIPT OUT OF RANGE:**
              Workbooks.Open(path & filename).Sheets("Sheet1").UsedRange.Find(BOM).Offset(0, 5).Copy

              'the macro never gets to this line: 
              ThisWorkbook.Sheets("Sheet1").Range("c" & Range("c5").End(xlDown).Row).Paste
              Workbooks.Open(path & filename).Close
              filename = Dir
        Loop

End If

End Sub

Answer 1

Вот мой подход к сближению с реальной проблемой:

Разделите очень длинную линию, которая вызывает проблему на более мелкие части, например

  Set wrkbk = Workbooks.Open(path & filename)
  Set sht = wrkbk.Sheets("Sheet1")
  Set rang = sht.UsedRange
  Set fnd = rang.Find(BOM)
  If Not (fnd Is Nothing) Then
     cpy = fnd.Offset(0, 5).Copy
  End If

Тогда вы обнаружите, что проблема в строке Set sht = wrkbk.Sheets("Sheet1") .

Это означает: есть одна рабочая книга, у которой нет листа под названием "Лист1". Возможно, лист был переименован пользователем.

Когда Excel останавливается на ошибке, щелкните правой кнопкой мыши на filename и выберите Add watch чтобы увидеть, какой файл влияет на неправильное имя листа.