Excel vba поиск объединенных ячеек
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.
Параметры метода Range.Find
- xlValues (-4163) – значения;
- xlComments (-4144) – примечания*;
- xlNotes (-4144) – примечания*;
- [xlFormulas (-4123) – формулы]**.
- xlWhole (1) – полное совпадение;
- xlPart (2) – частичное совпадение.
- xlByRows (1) – поиск по строкам;
- xlByColumns (2) – поиск по столбцам.
- xlNext (1) – поиск вперед;
- xlPrevious (2) – поиск назад.
- False (0) – поиск без учета регистра (по умолчанию);
- True (1) – поиск с учетом регистра.
- False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
- True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
В Excel вы можете использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух различных способах облегчения поиска и выделения результатов поиска в Excel.
Искать и выделять результаты поиска по коду VBA
В Excel есть код VBA, который может помочь вам найти значение и затем выделить результаты поиска.
1. Включите лист, который вы хотите найти, и выделите результаты, нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модульи вставьте приведенный ниже код в новый модуль.
VBA: выделение результатов поиска
3. Нажмите F5 нажмите клавишу, затем появится окно поиска, в котором вы можете ввести указанное вами значение.
4. Нажмите OK, совпадающие результаты были выделены цветом фона. Кроме того, появляется диалоговое окно с вопросом, отменить ли выделение. Нажмите OK чтобы отменить выделение и закрыть диалоговое окно, нажмите Отмена чтобы сохранить выделение и закрыть диалог.
Внимание:
1. Если подходящего значения не найдено, появится диалоговое окно с напоминанием.
2. Этот VBA работает для всего активного листа и нечувствителен к регистру.
Вы хотите повысить зарплату и много времени проводить с семьей?
Вкладка Office повышает эффективность работы Microsoft Office на 50% прямо сейчас
Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.
По сравнению с известными браузерами инструмент с вкладками в Office Tab более мощный и эффективный.
Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.
Если вы обычно работаете с несколькими документами, вкладка Office поможет вам сэкономить время.
30-дневная бесплатная пробная версия , кредитная карта не требуется.
Поиск и выделение результатов поиска с помощью условного форматирования
В Excel функция условного форматирования также может автоматически выделять результаты поиска.
Предположим, что данные и окно поиска отображаются, как показано на скриншоте ниже, теперь выполните следующие действия:
1. Выберите диапазон, в котором нужно выполнить поиск, и нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем перейдите в текстовое поле под Формат значений, где эта формула истинна, напишите = И ($ E $ 2 <> "", $ E $ 2 = A4) .
E2 - это ячейка, в которую вы поместите поисковое значение, а A4 - это первая ячейка в диапазоне, в котором вы выполняете поиск.
3. Нажмите Формат кнопку, перейдите к Формат ячеек диалога под Заполнять на вкладке выберите нужный цвет. Нажмите OK > OK чтобы закрыть диалоги.
С этого момента, когда вы вводите ключевое слово в ячейку E2, результаты поиска будут автоматически выделяться в указанном диапазоне.
Файл примера
Другие операции (статьи), связанные с форматированием Conditioanl
Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.
Создать диаграмму с условным форматированием в Excel
Например, у вас есть таблица оценок для класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах. В этом руководстве представлен метод решения этой задачи.
Гистограмма с накоплением условного форматирования в Excel
В этом руководстве показано, как создать столбчатую диаграмму с условным форматированием, как показано на скриншоте ниже, шаг за шагом в Excel.
Условное форматирование строк или ячеек, если два столбца равны в Excel
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
Применить условное форматирование для каждой строки в Excel
Иногда может потребоваться применить условное форматирование для каждой строки. Кроме многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи.
Обычно вы предпочитаете объединять все ячейки с одинаковыми значениями в диапазоне в Excel. Но в некоторых случаях может потребоваться подсчитать все объединенные ячейки в выбранном диапазоне. Как с этим бороться? В этой статье будут представлены два метода быстрого подсчета объединенных ячеек в Excel.
Подсчет объединенных ячеек в диапазоне в Excel с кодом VBA
1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модуль, и скопируйте VBA в модуль.
VBA: подсчет объединенных ячеек в диапазоне
3. Сохраните код, закройте окно и введите эту формулу. = CountMerged (A1: G10) (диапазон A1: G10 - это диапазон, из которого вы хотите подсчитать объединенные ячейки, вы можете изменить его по своему усмотрению) в пустую ячейку и нажмите Enter кнопку на клавиатуре. Затем подсчитанный результат отображается в ячейке, см. Снимок экрана:
Подсчет объединенных ячеек в диапазоне в Excel одним щелчком мыши
Здесь я покажу вам быстрый способ подсчета объединенных ячеек в диапазоне одним щелчком мыши с Выберите объединенные ячейки полезности Kutools for Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон с объединенными ячейками, который вы хотите подсчитать. А затем нажмите Kutools > Выберите > Выбрать объединенные ячейки. Смотрите скриншот:
2. Затем Kutools for Excel диалоговое окно всплывает с общим количеством объединенных ячеек, перечисленных внутри. Пожалуйста, нажмите на OK кнопка. Затем сразу выбираются все объединенные ячейки в выбранном диапазоне. Смотрите скриншот:
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
А как объединить эти два макроса в один?
у меня есть макрос, который правильно объединяет ячейки (в основном при работе с фильтрами),
у меня есть макрос, который находит объединенные ячейки,
у меня есть файл с более чем 20 000 неправильно объединенными ячейками.
как создать цикл, который бы все эти ячейки правилно объединил.
макросы прилагаются
((((((ПРАВИЛЬНАЯ ГРУППИРОВКА))))))
SUB ReMerge() ' перегруппировать сгруппированную ячейку или сгруппировать ячейки выделенного диапазона с заполнением скрытых ячеек формулами-ссылками на первую ячейку
IF TypeName(Selection) <> "Range" THEN EXIT SUB
IF Selection.Cells.Count <= 1 THEN EXIT SUB
DIM i%, iCell AS Range, ActRng AS Range
DIM ActSh AS Worksheet, TempSh AS Worksheet
DIM lLastRow&: lLastRow = Cells.SpecialCells(xlLastCell).Row
DIM lLastCol&: lLastCol = Selection.Column + Selection.Columns.Count - 1
IF lLastRow > Selection.Row + Selection.Rows.Count - 1 THEN lLastRow = Selection.Row + Selection.Rows.Count - 1
Set ActRng = Intersect(Selection, Range(Cells(Selection.Row, Selection.Column), Cells(lLastRow, lLastCol)))
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Set ActSh = ActiveSheet: Set TempSh = Sheets.Add ' запомнить текущую и создать новую страницу
ActRng.Copy TempSh.Range(ActRng.Address)
ActSh.Activate
Selection.UnMerge
FOR i = 2 TO ActRng.Cells.Count ' заполнить Selection формулами-ссылками на первую ячейку
ActRng(i).Formula = "=" & ActRng(1).Address
ActRng(i).Replace What:="$", Replacement:="", LookAt:=xlPart ' сделать ссылки перемещаемыми
NEXT
TempSh.Range(ActRng.Address).Merge
TempSh.Range(ActRng.Address).Copy: ActRng.PasteSpecial xlPasteFormats: TempSh.Delete
Set ActSh = Nothing: Set TempSh = Nothing: Set ActRng = Nothing
Application.ScreenUpdating = True: Application.DisplayAlerts = True
END SUB
SUB MCells()
Application.FindFormat.MergeCells = True
Cells.Find(What:="", After:=ActiveCell, MatchCase:=False, SearchFormat:=True).Activate
END SUB
0 ПОВТОРИТЬ ПОКА НЕ ДОСТИГЛИ КОНЦА ТАБЛИЦЫ
1 ПОИСК ЯЧЕЙКИ
2 ПРИМЕНЕНИЕ МАКРОСА
Читайте также: