Функция пользовательского поиска Excel vba: быстрая, когда используется вручную, медленная макрокоманда

Мне пришлось написать свою собственную функцию поиска, но ее производительность сильно отличается в зависимости от того, назову ее вручную вручную на листе (через «=») или с помощью макросов. Мои макросы делают следующее:

Function betterSearch(searchCell As Range, aCol As Range, bCol As Range) For Each cell In aCol If LCase(cell.Value) = LCase(searchCell.Value) Then betterSearch = bCol.Cells(cell.row, 1) Exit For End If betterSearch = "Not found" Next End Function 

Таким образом, макросы открывают resultsWorkbook и dataWorkbook а затем выполняют поиск четырех значений в столбце A из resultWorkbook в dataWorkbook и возвращают соответствующие данные из другого столбца dataWorkbook .

 '...opening resultWorkbook and dataWorkbook For aRow = 6 To 9 resultWorkbook.Worksheets("B3").Cells(aRow, 125).Value = _ betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _ , dataWorkbook.Worksheets("page 1").Range("A:A") _ , dataWorkbook.Worksheets("page 1").Range("Z:Z")) resultWorkbook.Worksheets("B3").Cells(aRow, 126).Value = _ betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _ , dataWorkbook.Worksheets("page 1").Range("A:A") _ , dataWorkbook.Worksheets("page 1").Range("I:I")) Next aRow 

Это очень медленно – требуется минут для 1 файла. Но когда я открываю этот файл вручную и просто набираю формулу и нажимаю Enter, он вычисляет мгновенно.

Что здесь не так? Откуда такое поведение?

Это может быть медленным, если он пытается выполнить поиск по всем столбцам A: A, I: I (1+ миллионов ячеек)

Попробуйте обновить свой лучший поиск до:

 Function betterSearch(searchCell As Range, aCol As Range, bCol As Range) Dim itm As String, col1 As Variant, col2 As Variant, r As Long With ActiveSheet col1 = .UsedRange.Columns(aCol.Column) col2 = .UsedRange.Columns(bCol.Column) End With betterSearch = "Not found" itm = LCase(searchCell.Value2) For r = 1 To UBound(col1) If Len(col1(r, 1)) > 0 Then If LCase(col1(r, 1)) = itm Then betterSearch = col2(r, 1) Exit Function End If End If Next End Function 
Interesting Posts

Попробуйте создать папку с помощью Mac VBA

Найти одну ячейку внутри строки, а затем заполнить другую ячейку, основанную на этом

Можно просмотреть исходный код или декомпилировать XLL Excel добавить в

SUMIFS, с критериями <или>

Excel: автоматическое копирование значения, количество раз эквивалентное количеству ненулевых ячеек в той же строке

Как копировать формулы, содержащие определенные имена, и разблокировать ядро ​​refrence в excel 2016?

Загрузите Excel в таблицы DB, используя хранимые процедуры

Ошибка при получении таблицы из набора записей VBA

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

Как узнать, какой динамический контроль отправлен на событие?

Проблема с динамикой диаграммы с использованием имени формулы

Преобразование excel в JSON с использованием C # с первой строкой в ​​excel, служащей в качестве ключей

Как я могу пропустить ячейки в цикле, содержащем строку, используя VBA

Создать листы из списка в excel с помощью шаблона

Как избежать ошибок во время выполнения при доступе к свойству PageSetup

Давайте будем гением компьютера.