Как проверить есть ли значение в столбце excel vba
Возвращает значение из диапазона одной строки или столбца или массива. Функция Lookup имеет две формы синтаксиса: форму вектора и форму массива.
Синтаксис
выражения. Lookup (Arg1, Arg2, Arg3)
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Variant | Lookup_value — значение , которое lookup ищет в первом векторе. Lookup_value может быть числом, текстом, логическим значением или именем или ссылкой, которая ссылается на значение. |
Arg2 | Обязательный | Variant | Lookup_vector или Array — в векторной форме диапазон, содержащий только одну строку или один столбец. Значения в lookup_vector могут быть текстовыми, цифрами или логическими значениями. В форме массива набор ячеек, содержащий текст, цифры или логические значения, которые необходимо сравнить с lookup_value. |
Arg3 | Необязательный | Variant | Result_vector — используется только с векторной формой. Диапазон, содержащий только одну строку или столбец. Он должен быть того же размера, что и lookup_vector. |
Возвращаемое значение
Variant
Примечания
Если нужно | См. | Применение |
---|---|---|
Посмотрите в диапазоне один ряд или один столбец (известный как вектор) для значения и верните значение из одной позиции во втором диапазоне одной строки или одного столбца. | Векторная форма | Используйте форму вектора, когда у вас есть большой список значений, чтобы посмотреть вверх или когда значения могут изменяться с течением времени. |
Посмотрите в первом ряду или столбце массива указанное значение и верните значение из того же положения в последнем ряду или столбце массива. | Форма массива | Используйте форму массива, если у вас есть небольшой список значений и значения остаются неизменными с течением времени. |
Векторная форма
Вектор — это диапазон только одной строки или столбца. Векторная форма Lookup выглядит в диапазоне один ряд или один столбец (известный как вектор) для значения и возвращает значение из того же положения во втором диапазоне одной строки или одного столбца. Используйте эту форму функции Lookup , когда необходимо указать диапазон, содержащий значения, которые необходимо соответствовать. Другая форма Lookup автоматически выглядит в первом столбце или строке.
Значения в lookup_vector должны быть помещены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE; в противном случае Lookup может не дать правильное значение. Текст uppercase и lowercase эквивалентны.
Если lookup не может найти lookup_value, он соответствует наибольшему значению в lookup_vector, которое меньше или равно lookup_value.
Форма массива
Форма массива Lookup выглядит в первом ряду или столбце массива для указанного значения и возвращает значение из той же позиции в последнем ряду или столбце массива. Используйте эту форму Lookup , когда значения, которые необходимо соответствовать, находятся в первом ряду или столбце массива. Используйте другую форму Lookup , когда нужно указать расположение столбца или строки.
В общем случае вместо формы массива Lookup лучше использовать функцию HLookup или VLookup. Эта форма Lookup предоставляется для совместимости с другими программами таблиц.
Если lookup не может найти lookup_value, он использует самое большое значение в массиве, которое меньше или равно lookup_value.
Форма массива Lookup очень похожа на функции HLookup и VLookup . Разница заключается в том, что HLookup ищет lookup_value в первом ряду, VLookup выполняет поиск в первом столбце, а Поиск поиска в соответствии с размерами массива.
Если массив охватывает область, которая шире, чем высокая (больше столбцов, чем строк), поиск поиска lookup_value в первом ряду.
Если массив квадратный или больше ширины (больше строк, чем столбцов), поиск поиска в первом столбце.
С помощью HLookup и VLookup можно индексировать вниз или поперек, но Lookup всегда выбирает последнее значение в строке или столбце.
Значения массива должны быть размещены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE; в противном случае Lookup может не дать правильное значение. Текст uppercase и lowercase эквивалентны.
Вы также можете использовать функцию Lookup в качестве альтернативы функции IF для сложных тестов или тестов для более чем семи условий. Примеры см. в форме массива.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
у меня есть следующий столбец (столбец A) с именем project (столбец строк просто отображает номер строки):
моя теория заключалась в том, чтобы найти номер строки первого "Projectemp", а затем вставить новую строку, где проект равен 20.
I пытался использовать функцию Find, но я получаю ошибку переполнения (я уверен, что получаю ее, потому что она находит 3 строки "ProjTemp" и пытается установить его в один параметр):
как мне это кодировать, чтобы я нашел только номер строки кулака "ProjTemp"? Есть ли лучший способ сделать это, может быть цикл?
спасибо, любая помощь будет оценена!
Я действительно не знаком со всеми этими параметрами Find метод; но после его сокращения для меня работает следующее:
и если вам нужен только номер строки, вы можете использовать это после:
Если вы хотите получить дополнительные, вы можете использовать:
в качестве альтернативы вы можете использовать цикл, сохранить номер строки (счетчик должен быть номером строки) и остановить цикл, когда вы найдете первый "ProjTemp".
Тогда это должно выглядеть примерно так:
- поскольку позиция поиска важна, вы должны указать, где вы начинаете поиск. Я использую ws.[a1] и xlNext ниже, поэтому мой поиск начинается в A2 из указанного листа.
- часть Find s аргументы-в том числе lookat используйте предыдущие настройки поиска. Поэтому вы всегда должны указывать xlWhole или xlPart чтобы соответствовать всем или части строки соответственно.
- вы можете сделать все, что хотите, включая вставку строка и запрос пользователю нового значения (мой код предложит 20, если Предыдущее значение было 19) без использования Select или Activate
предложил код
Проверьте "projtemp", а затем проверьте, является ли предыдущая запись номером (например, 19,18..так далее..) если это так, то получите строку no этого proj temp .
и если это не так ..затем повторно проверьте, что предыдущая запись является projtemp или записью номера .
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Синтаксис
выражение.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
What | Обязательный | Variant | Искомые данные. Может быть строкой или любым типом данных Microsoft Excel. |
After | Необязательный | Variant | Ячейка, после которой нужно начать поиск. Соответствует положению активной ячейки, когда поиск выполняется из пользовательского интерфейса. |
Обратите внимание, что параметр After должен быть одной ячейкой в диапазоне. Помните, что поиск начинается после этой ячейки; указанная ячейка не входит в область поиска, пока метод не возвращается обратно в эту ячейку.
Возвращаемое значение
Объект Range, представляющий первую ячейку, в которой обнаружены требуемые сведения.
Примечания
Этот метод возвращает значение Nothing, если совпадения не найдены. Метод Find не влияет на выделенный фрагмент или активную ячейку.
Параметры для аргументов LookIn, LookAt, SearchOrder и MatchByte сохраняются при каждом использовании этого метода. Если не указать значения этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет параметры в диалоговом окне Найти, а изменение параметров в диалоговом окне Найти приводит к изменению сохраненных значений, которые используются, если опустить аргументы. Чтобы избежать проблем, устанавливайте эти аргументы явным образом при каждом использовании этого метода.
Для повторения поиска используйте методы FindNext и FindPrevious.
Когда поиск достигает конца указанного диапазона поиска, он возвращается в начало диапазона. Чтобы остановить поиск при этом возврате, сохраните адрес первой найденной ячейки, а затем проверьте адрес каждой последующей найденной ячейки, сравнив его с этим сохраненным адресом.
Чтобы найти ячейки, отвечающие более сложным шаблонам, используйте инструкцию For Each. Next с оператором Like. Например, следующий код выполняет поиск всех ячеек в диапазоне A1:C5, где используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит соответствующее значение, шрифт изменяется на Times New Roman.
Примеры
В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие значение 2, и изменить значение ячейки на 5. Таким образом, значения 1234 и 99299 содержали 2 и оба значения ячеек станут 5.
В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие подстроку "abc", и изменить ее на "xyz".
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Я пытаюсь написать VBA процедура, которая будет принимать строку, искать данную книгу Excel и возвращать мне все возможные совпадения.
в настоящее время у меня есть реализация, которая работает, но она очень медленная, поскольку это двойной цикл for. Конечно, встроенный Excel Find функция "оптимизирована", чтобы найти одно совпадение, но я хотел бы вернуть массив начальных совпадений, к которым я могу применить дальнейшие методы.
я опубликую некоторый псевдокод из того, что у меня уже есть
как уже говорилось ранее, этот двойной цикл for делает вещи работать очень медленно, поэтому я ищу, чтобы избавиться от этого, если это возможно. Есть предложения?
обновление
в то время как приведенные ниже Ответы улучшили бы мой метод, я закончил с чем-то немного другим, поскольку мне нужно было делать несколько запросов снова и снова.
вместо этого я решил перебрать все строки в моем документе и создать словарь, содержащий ключ для каждой уникальной строки. Значение, на которое это указывает, будет списком возможных совпадений, поэтому, когда я запрошу позже, я могу просто проверить, существует ли он, и если да, просто получить быстрый список совпадений.
в основном просто делает одну начальную развертку, чтобы сохранить все в управляемой структуре, а затем запросить эту структуру, которая может быть выполнена в O(1) времени
использование диапазона.Метод Find, как указано выше, наряду с циклом для каждого листа в книге, является самым быстрым способом сделать это. Следующее, например, находит строку "вопрос?"в каждом листе, и заменяет его строкой "ответил!".
осторожно при выполнении цикла поиска, что вы не попадаете в бесконечный цикл. Ссылка на первый найденный адрес ячейки и сравнение после каждого оператора" FindNext", чтобы убедиться, что он не вернулся к первой изначально найденной ячейке.
вы можете использовать диапазон.Найти метод:
Это даст вам первую ячейку, которая содержит строку поиска. Повторяя это с установкой аргумента "после" в следующую ячейку, вы получите все остальные вхождения, пока не вернетесь к первому вхождению.
Это, вероятно, будет намного быстрее.
вы можете прочитать данные в массив. Оттуда вы можете сделать матч в памяти, а не читать по одной ячейке за раз.
ниже код позволяет избежать создания бесконечного цикла. Предположим, что XYZ-это строка, которую мы ищем в книге.
основываясь на идее ответа B Hart, вот моя версия функции, которая ищет значение в диапазоне и возвращает все найденные диапазоны (ячейки):
для поиска значения во всей книге:
в своем сценарий, Я должен искать значение в столбце A и нужно выяснить совпадения в столбце B. Поэтому я создал цикл for, внутри него будет смотреть на весь столбец A и получить точное совпадение из столбца B.
Читайте также: