Блог

Главная - Вопросы по программированию - VBA: использование формулы массива

VBA: использование формулы массива

#vba #array-formulas

#vba #массив-формулы

Вопрос:

Я хочу найти номер строки на основе двух критериев в столбцах C и E. Мои данные выглядят следующим образом:

введите описание изображения здесь

Я погуглил свою проблему, и использование Match функции в качестве формулы массива работает для этого (работало, когда я использовал ее в Excel, а не в VBA), но я не могу понять, как сделать ее формулой массива в VBA. Различные решения, будь то использование «[]» или .Evaluate не сработали для меня (возможно, это была моя ошибка). Итак, как мне изменить этот код, чтобы получить желаемый результат:

 Sub Test1()
    Dim rowDB As Long
    Dim wsDB As Worksheet
    
    Set wsDB = ActiveSheet
    rowDB = WorksheetFunction.Match(CDate("30.06.2020") amp; "EX0500-0001", wsDB.Range("C7:C366") amp; wsDB.Range("E7:E366"))
End Sub

Я получаю ошибку «ошибка 13: несоответствие типов», поэтому я не уверен, есть ли здесь другая проблема или просто отсутствие формулы массива.

Ответ №1:

Я немного поиграл с этим и обнаружил несколько проблем:

Похоже, что CDate() не нравится «30.06.2020» в качестве входных данных и выдает ошибку типа. Кажется, он доволен «30-06-2020», так что, возможно, вместо этого используйте этот формат или просто найдите строку «30.06.2020»? Это должно быть нормально, если все форматы даты согласованы.

Worksheetфункция.Второй параметр Match() должен быть непрерывным диапазоном, а ваш — нет. Также я не думаю, что выражение wsDB.Range("C7:C366") amp; wsDB.Range("E7:E366") имеет смысл; если вы хотите объединить диапазоны, используйте функцию Union() . Но здесь это не сработает, потому что, как уже упоминалось, диапазон не является непрерывным.

Я не думаю, что можно использовать WorksheetFunction.Match() для поиска нескольких значений, поэтому вам, возможно, придется искать дату в столбце C и строку в столбце E отдельно.

Вот некоторый vba, над которым я работал, просто для поиска одного значения:

 Sub Test4()
    Dim rowDB As Long
    Dim wsDB As Worksheet

    Set wsDB = ActiveSheet
    rowDB = WorksheetFunction.Match("30.06.2020", wsDB.Range("C7:C366"))
    Debug.Print rowDB
End Sub

Кроме того, если совпадение не найдено, будет выдана «Ошибка, определенная приложением или объектом», поэтому вам нужно будет реализовать некоторую обработку ошибок.

Комментарии:

1. Спасибо за вашу помощь. Я не уверен в функции VBA, но в Excel вы можете использовать Match с несколькими критериями: в моей немецкой версии Excel =VERGLEICH(DATWERT("30.06.2020") amp; "EX0500-0001"; C7:C366 amp; E7:E366;0) работает для меня, что в английской версии должно быть =MATCH(DATVALUE("30.06.2020") amp; "EX0500-0001", C7:C366 amp; E7:E366,0) как для отдельного просмотра столбца n: будет ли это означать два цикла для каждого? Оба столбца имеют повторяющиеся значения, например, EX0500-0001 для более чем одной даты, так что что-то вроде .Find() не помогло бы, верно?

2. Я еще немного повозился с этим, что касается CDate(): похоже, я получал ошибку типа из-за регионального формата даты Windows. Когда я изменил его на D-M-Y, он работал нормально. Я не мог заставить формулу Excel работать на меня, я не знаю почему. Что касается vba: используйте цикл For, чтобы проверить каждую ячейку в столбце C, чтобы увидеть, соответствует ли она значению даты, если вы найдете совпадение для этой строки, затем проверьте, соответствует ли значение в столбце E вашему другому значению: если оно совпадает, то вы закончили, если нет, проверьтеследующая ячейка в столбце C и т.д…