Vba excel поиск даты
Я борюсь с одним простым кодом, который не сотрудничает со мной ;) У меня есть в столбце L некоторые даты, начинающиеся со строки 5 . Даты находятся в формате yyyy.mm , но в строке формул видна вся дата в формате dd.mm.yyyy .
В столбце F находятся те же даты, отформатированные таким же образом. Разница лишь в том, что здесь некоторые ячейки объединены. Каждая дата находится в ячейке, которая объединяется со следующими 2 ячейками.
Это значит, что в ячейке F5 стоит дата.
Эта ячейка объединяется с F6 и F7 .
В ячейке F8 - следующая дата. Эта ячейка объединяется…
Я хотел бы взять первую дату из столбца L и найти их в столбце F , а если найду, то сделать некоторые действия.
Проблема в том, что переменная Dates каждый раз пуста. Буду признателен за любой совет.
С уважением Неке
2 ответа
У меня есть небольшая таблица Excel, которая ежедневно обновляется пользователями. Он содержит столбец даты. Я поместил небольшой фрагмент VBA, который отправляет предупреждение по электронной почте, когда столбец даты содержит дату, равную текущей дате, и затем закрывает файл. Этот Excel.
Я успешно вытягиваю данные в excel через SQL запрос, отформатированный так же в VBA: .Open exec sp_MyProcedure @Node_Id = 05,@Subsidiary_Cd = '1',@WeekEndDate = '2016-05-28', @JobType = '12',@ReportLevel = 4 Я хотел бы либо попросить пользователя ввести самую последнюю дату окончания недели.
Я просмотрел ваш код. Вы ищете диапазон x. Я предполагаю, что простое смещение не сработает, потому что даты в столбце F и L не в том же порядке? ваш код:
Если простое смещение сработает, вы можете попробовать:
если вы ищете дату, попробуйте добавить .value в конце.
Или вместо поиска диапазона найдите дату. Я никогда не искал диапазон в Excel vba. Я всегда "read" значение ячейки в переменной, а затем ищу его. Но это вызывает проблемы, если есть дубликаты.
Пожалуйста, дайте мне знать, если это поможет.
Поскольку никто не решил мою проблему, я сделал это сам.
Еще раз, чтобы внести ясность. В обеих колонках я написал дату dd.mm.yyyy. Формат всех ячеек является пользовательским (yyyy.mm), так что в ячейках отображается 2017.07, но в строке формул все равно что-то вроде 01.07.2017.
Приведенный выше макрос не сработал, потому что метод find был неправильно использован. Я изменился:
LookIn:xlFormulas ' вместо того, чтобы искать значение ячейки, я просто беру информацию из строки формул. Понятия не имею, почему xlValues не работает. Значения те же самые.
SearchOrder: xlColumns ' это изменение позволяет мне найти то, что я ищу, в объединенных ячейках.
Может быть, это кому-то поможет.
Похожие вопросы:
у меня есть мой баланс в excel, есть две колонки, над которыми я хочу работать, одна-рассрочка, другая - дата. таким образом, если значение рассрочки уменьшается на единицу, то дата месяца должна.
Я понимаю, что заставить excel импортировать данные и распознавать их как дату-это обычная проблема. Я просмотрел много предыдущих постов с ответами, но пока не нашел решения: Вот как выглядят.
(Excel 2010 VBA) У меня есть ячейка (A1), содержащая дату в формате mmm-yy (категория Custom). Например, если я ввожу 1/6/13, то ячейка показывает июнь-13. - Все в порядке. В моем макросе VB мне.
У меня есть небольшая таблица Excel, которая ежедневно обновляется пользователями. Он содержит столбец даты. Я поместил небольшой фрагмент VBA, который отправляет предупреждение по электронной.
Я успешно вытягиваю данные в excel через SQL запрос, отформатированный так же в VBA: .Open exec sp_MyProcedure @Node_Id = 05,@Subsidiary_Cd = '1',@WeekEndDate = '2016-05-28', @JobType =.
Я хотел бы знать, есть ли способ удаленно деактивировать файл excel через vba. Проблема: Моя компания использует файл excel для продажи, чтобы предоставить клиенту котировки. Теперь, когда есть.
Я использую Excel VBA, как я могу ? Введите месяц и год и верните все номера недель за этот месяц. Введите дату и получите номер недели для этой даты.
У меня есть приложение Excel/ VBA, которое запускает запрос на основе предоставленных дат начала и окончания. Даты начала и окончания указаны на самом листе Excel (не на бланках). Требование состоит.
Итак, у меня возникли некоторые проблемы с датами, которые меняются местами в VBA при назначении переменной даты. Это проще, чем кажется, но это действительно беспокоит меня. Код: Dim InsertedDate.
ВНИМАТЕЛЬНО ПРОЧТИТЕ ВОПРОС, ПОЖАЛУЙСТА, В НЕМ УПОМИНАЕТСЯ EXCEL VBA. Не EXCEL Мне было поручено установить дату input на бланке VBA. A textbox должен заставить пользователя ввести дату как.
есть функция на VBA, которая на вход получает номер строки, на основании которого записывает в переменную из нужного столбца дату. Функция должна найти в определенной строке (в таблице это временная шкала) эту дату и сохранить номер столбца результата. Сейчас это выглядит примерно так:
Проблема в том, что находит эту же самую ячейку, которая грубо говоря задается условием. Пробовал разные варианты поиска, нужного результата не получил( В "заданной" ячейке дата в формате ДД.ММ.ГГГГ , в искомой МММ ДД ГГ . Заранее всем спасибо)
Оператор With указывает на родителя, а между With-End With указываются подчиненные объекты. Но не все, что внутри, относится к родителю.
Запишем данные диапазона в массив. Ссылку на диапазон листа пишем обычно так - родитель(точка)подчиненный:
Объявим объект с помощью With :
Здесь диапазон A1:C5 относится к листу "Лист", а диапазон D1:E5 - к активному листу.
Теперь, надеюсть, ошибка понятна.
Еще один момент, который иногда ставит подножки (вернее, авторы кода сами себе посечку делают).
Это может быть и диапазон, и значение. Хорошо, что объявленная переменная d которой передается объект, имеет тип Date , но все же рекомендую не экономить на символах и, когда нужно получить значение ячейки, явно это прописывать:
Таблицу аргументов (значений) параметра interval смотрите в параграфе «Приложение 1».
Примечание к таблице аргументов: три символа – y, d, w – указывают функции DateAdd на один день, который необходимо прибавить к исходной дате number раз.
Пример
MsgBox "31.01.2021 + 1 месяц crayon-h"> & DateAdd ( "m" , 1 , "31.01.2021" ) 'Результат: 28.02.2021 MsgBox "Сегодня + 3 года crayon-h"> & DateAdd ( "yyyy" , 3 , Date ) MsgBox "Сегодня - 2 недели crayon-h"> & DateAdd ( "ww" , - 2 , Date ) MsgBox "10:22:14 + 10 минут crayon-h"> & DateAdd ( "n" , 10 , "10:22:14" ) 'Результат: 10:32:14Функция DateDiff
DateDiff – это функция, которая возвращает количество указанных интервалов времени между двумя датами. Тип возвращаемого значения – Variant/Long.Синтаксис
DateDiff ( interval , date1 , date2 , [ firstdayofweek ] , [ firstweekofyear ] )Параметры
| Параметр | Описание |
|---|---|
| interval | Обязательный параметр. Строковое выражение из спецсимволов, представляющее интервал времени, количество которых (интервалов) требуется вычислить между двумя датами. |
| date1, date2 | Обязательные параметры. Значения типа Variant/Date , представляющие две даты, между которыми вычисляется количество указанных интервалов. |
| firstdayofweek | Необязательный параметр. Константа, задающая первый день недели. По умолчанию – воскресенье. |
| firstweekofyear | Необязательный параметр. Константа, задающая первую неделю года. По умолчанию – неделя, в которую входит 1 января. |
Таблицу аргументов (значений) параметра interval смотрите в параграфе «Приложение 1».
Примечание к таблице аргументов: в отличие от функции DateAdd , в функции DateDiff спецсимвол "w" , как и "ww" , обозначает неделю. Но расчет осуществляется по разному. Подробнее об этом на сайте разработчиков.
Параметры firstdayofweek и firstweekofyear определяют правила расчета количества недель между датами.
Таблицы констант из коллекций firstdayofweek и firstweekofyear смотрите в параграфах «Приложение 2» и «Приложение 3».
Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.
Поиск перебором значений
Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:
Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую "123" достаточно такого кода:
Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What - Строка с текстом, который ищем или любой другой тип данных Excel
After - Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").
Примеры поиска функцией Find
Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
В ниже следующем примере используется другой вариант продолжения поиска - с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")
Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.
Свойство FindFormat можно задавать разными способами, например, так:
Поиск последней заполненной ячейки с помощью Find
Следующий пример - применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;
- для обозначения символов *, ? и
. (т.е. чтобы искать в тексте вопросительный знак, нужно написать
?, чтобы искать именно звездочку (*), нужно написать
* и наконец, чтобы найти в тексте тильду, необходимо написать
Поиск в скрытых строках и столбцах
Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
Пример 8: Найти 1 марта 2018 г.
Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
Книги по теме:
Посмотреть все книги по программированию
Комментарии к статье:
| 10.09.17 Дмитрий | Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо! |
| 23.11.17 Гость | Спасибо, хорошая статья. |
| 03.12.17 Владимир | Спасибо! Использую в своих проектах. |
| 07.12.17 Эд | Спасибо, очень пригодилась Ваша статья! |
| 19.01.18 Николай | .find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра. |
| 05.02.18 Владимир | Большое спасибо! Очень толково и понятно. |
| 11.03.18 Гость | Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать? |
| 26.03.18 Гость | Спасибо! Все бы так описывали! Все доступно и понятно)) |
| 23.05.18 Аркадий | В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках. |
| 21.07.18 Гость | Спасибо! Уже несколько недель искала подобное! |
| 25.07.18 Joann | Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)? |
| 15.08.18 Марат | Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент! |
| 28.08.18 Гость | Познавательно и подробно. Спасибо за статью. |
| 16.10.18 Гость | Хорошая статья. Спасибо |
| 29.10.18 Sega | Полезная статейка. Спасибо. |
| 14.11.18 Гость | Статья для начинающих, а тонкости поиска по дате нет ни одного примера |
| 02.02.19 Ибрагим | Чушь полная, плагиат! |
| 01.03.19 inexsu.wordpress.com | Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/ |
| 01.03.19 Администратор | Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку. |
| 07.04.19 Гость | Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find. |
| 07.04.19 Гость | Лучше использовать функцию ВПР |
| 21.05.19 Гость | Очень подробная статья. Отдельное спасибо за множество примеров. |
| 17.10.19 Михаил | Спасибо большое все понятно доступно и очень полезно |
| 31.10.19 Гость | Спасибо. |
| 01.11.19 Гость | ку |
| 01.11.19 Гость | 2 раза |
| 19.11.19 Kamol | Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456" |
| 25.12.19 Гость | Спасибо! |
| 05.03.20 Гость | Добрый день! Спасибо за код автору Вопрос |
Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
Читайте также: