VBA: как определить содержится ли в массиве данное значение

[Rimida]

Коллеги, помогите оптимизировать макрос.
Использую часто повторяющуюся операцию: ищу среди ячеек одной колонки листа совпадающее значение. Сейчас использую цикл Do While или комбинацию While Wend + If Then с перебором всех ячеек с помощью Cells (i, k):

While i <= dataRows
If CLng(.Cells(i, 9).Value) = EDR Then
Worksheets("Customers").Cells(k, 2).Value = .Cells(i, 11).Value
i = dataRows
End If
i = i + 1
Wend

Но подозреваю, что существует более оптимальный путь. Например, сформировать одномерный массив из значений колонки и затем с помощью какой-то функции (какой?) определить номер ячейки, совпадающей с заданным значением. Есть идеи?

[Hugo121]

В массиве можно найти соответствие с помощью Match, но совсем недавно, вроде даже на этом форуме, мы выяснили, что простым перебором массива не только быстрее, но и надёжнее получить результат.

Вот, нашёл: http://www.programmersforum.ru/showt...F1%F2%F0%E5%E5

Вообще поиск по листу можно делать с помощью Find - это быстрее, чем перебор ячеек.

Sub test()
    ' если значение ячейки "А1" не содержится в массиве "B1:B10"
    If [B1:B10].Find([A1], , , xlWhole) Is Nothing Then
        MsgBox """А1"" НЕ содержится в массиве ""B1:B10"""
    Else
        MsgBox """А1"" содержится в массиве ""B1:B10"""
    End If
End Sub

Или такой вариант, вроде как без поиска:

Sub test1()
    ' если значение ячейки "А1" содержится в массиве "B1:B10"
    If [or(B1:B10=A1)] Then
        MsgBox """А1"" содержится в массиве ""B1:B10"""
    Else
        MsgBox """А1"" НЕ содержится в массиве ""B1:B10"""
    End If
End Sub

Но если перебирать массив, то скорость уже сравнима, а удобств больше - можно легко дополнительные условия отбора добавить.

[Rimida]

Супер! За метод Find спасибо. Действительно получается быстрее. Ищу ячейку с нужными данными, определяю номер ее строки и провожу необходимые действия с другими ячейками данной строки.

[Rimida]

Блин, Find не работает :-(
Не могу понять причину.

Dim b
Dim FindCell As Range
Dim Bus As String

b = 5784437
Set FindCell = Worksheets("Checklist").Columns(1). Find(b, LookIn:=xlValues)
If Not FindCell Is Nothing Then
Bus = Worksheets("Checklist").Cells(FindC ell.row, 6).Value
MsgBox FindCell.row & " - " & Bus & " - " & Worksheets("Checklist").Cells(FindC ell.row, 2).Value
Else
Bus = "?"
MsgBox Bus
End If

Заданное значение не находит, хотя оно там точно есть!

[Hugo121]

Добавьте в параметры частичный поиск xlPart - может пробелы мешают.
Вручную через поиск находит это значение?

[Rimida]

xlPart не помогает
вручную - находит!
что обидно - сначала в режиме тестирования все заработало, а потом вдруг перестало, причину найти не могу

[Hugo121]

Код рабочий (если убрать два закравшихся пробела), я проверил.
Без примера с ошибкой больше сказать нечего.

[Rimida]

Может быть проблема в формате данных? На текстовых данных все работает. С числовыми - нет.
ОК, если можно еще вопрос по оптимизации. Теоретически работа с массивом данных проходит быстрее, чем постоянное обращение к ячейкам листа. Соответственно, можно скопировать нужный мне диапазон листа в массив, обработать его, а затем записать обратно. Вопрос: как это сделать эффективнее? Что-то я не могу найти нужный оператор/функцию.

[Hugo121]

В А1:А3 цифры.

Option Explicit

Sub test()
Dim a(), i As Long
a = [a1:a3].Value
For i = 1 To UBound(a)
a(i, 1) = a(i, 1) + 1
Next
[b1].Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

или выгрузка
[b1:b3].Value = a
или
[b1:b3] = a
или, чтоб понять суть:
[b1].Resize(3, 1) = a