Макрос поиска и замены в Excel
Обновлено: 20.11.2024
Возвращает логическое значение, указывающее символы в ячейках в указанном диапазоне. Использование этого метода не изменяет ни выделение, ни активную ячейку.
Синтаксис
выражение.Replace (What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
выражение Переменная, представляющая объект Range.
Параметры
| Имя | Обязательный/Необязательный | Тип данных | Описание |
|---|---|---|---|
| Что | Обязательно | Вариант | Строка, которую должен искать Microsoft Excel. |
| Замена | Обязательно | Вариант | Строка замены. |
| LookAt | Необязательно | Вариант | Может быть одной из следующих констант XlLookAt: xlWhole или xlPart. |
| SearchOrder< /td> | Необязательно | Вариант | Может быть одной из следующих констант XlSearchOrder: xlByRows или xlByColumns. |
| MatchCase | Необязательно | Вариант | True, чтобы сделать поиск чувствительным к регистру. |
| MatchByte | Необязательно | Вариант | Используйте этот аргумент, только если вы выбрали или установили поддержку двухбайтового языка в Microsoft Excel . Значение true, чтобы двухбайтовые символы соответствовали только двухбайтовым символам. False, чтобы двухбайтовые символы соответствовали их однобайтовым эквивалентам. |
| SearchFormat | Необязательно | Вариант | формат поиска для метода. |
| ReplaceFormat | Необязательно | Вариант | Формат замены для метода. |
Возвращаемое значение
Примечания
Настройки для LookAt, SearchOrder, MatchCase и MatchByte сохраняются каждый раз, когда вы используете этот метод. . Если вы не укажете значения для этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет настройки в диалоговом окне «Найти», а изменение настроек в диалоговом окне «Найти» изменяет сохраненные значения, которые используются, если аргументы опущены. Во избежание проблем задавайте эти аргументы явно при каждом использовании этого метода.
Пример
В этом примере каждое вхождение тригонометрической функции SIN заменяется функцией COS. Диапазон замены — столбец A на Листе 1.
Поддержка и обратная связь
Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.
Блог Excel, PowerPoint и MS Word, содержащий удобные и креативные фрагменты кода VBA. Эти коды макросов хорошо прокомментированы и полностью функциональны при копировании в модуль.
Что делает этот код VBA
Эти макросы VBA покажут вам различные способы поиска и замены all для любого текстового или числового значения.
Найти/заменить все на определенном листе
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
fnd = "апрель"
rplc = "май"
'Сохранение определенного листа в переменной
Set sht = Sheets("Sheet1")
'Выполнить поиск/замену всех
шт.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False , ReplaceFormat:=False
Найти/заменить все во всей книге
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
fnd = "апрель"
rplc = "май"
Для каждого sht в ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False , ReplaceFormat:=False
Следующее
Несколько итераций поиска/замены одновременно!
Если вам нужно одновременно выполнить несколько действий по поиску и замене, вы можете использовать массивы для хранения значений.
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("Канада", "США", "Мексика")
rplcList = Array("CAN", "США", "MEX")
'Перебрать каждый элемент в списках Array
For x = LBound (fndList) To UBound (fndList)
'Перебрать каждый рабочий лист в ActiveWorkbook
For Every sht In ActiveWorkbook.Worksheets < br />sht.Cells.Replace What:=fndList(x), Replace:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat :=False, ReplaceFormat:=False
Следующее
Несколько итераций одновременного поиска/замены (подача из таблицы)
Вы также можете прочитать мой пост, описывающий, как вы можете заполнить свои переменные массива из электронной таблицы Excel, если у вас уже есть удобный список в вашей электронной таблице (иногда это может быть проблемой, вводя все ваши значения внутри вашего кода VBA). Ниже приведен пример того, как это можно сделать.
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
'Создать переменную, указывающую на вашу таблицу
Set tbl = Worksheets("Sheet1").ListObjects("Table1")
'Создать массив из данных таблицы
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
'Назначить столбцы для поиска/замены данных
fndList = 1
rplcList = 2
'Перебрать каждый элемент в списках массивов
For x = LBound (myArray, 1) To UBound (myArray, 2)
'Перебрать каждый рабочий лист в ActiveWorkbook (пропустить лист с таблицей в нем)
Для каждого файла в ActiveWorkbook.Worksheets
Если имя файла <> tbl.Parent.Name, то
шт.Cells.Replace What:=myArray(fndList,x), Replace:=myArray(rplcList,x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ < br />SearchFormat:=False, ReplaceFormat:=False
Конец, если
Следующий шт
Следующий x
Уведомление о том, сколько ячеек было изменено
Возможно, вам захочется сообщить, сколько изменений было внесено после запуска вашего кода. Вы можете сделать это, используя функцию COUNTIF(), чтобы подсчитать, сколько ячеек содержит значение поиска, прежде чем вы действительно выполните поиск и замену.
Единственным недостатком функции СЧЁТЕСЛИ() является то, что она не будет подсчитывать несколько вхождений в одной ячейке. Я не мог найти способ обойти это, и если вы знаете, как это сделать, сообщите мне об этом в разделе комментариев (эта статья может указать вам правильное направление).
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim ReplaceCount As Long
fnd = "апрель"
rplc = "май"
Для каждого файла в ActiveWorkbook.Worksheets
ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")
шт.Cells.Replace what:=fnd, Replace:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False , ReplaceFormat: =Ложь
MsgBox "Я завершил поиск и сделал замены в ячейках " & ReplaceCount & "."
Как изменить это в соответствии со своими потребностями?
Возможно, это сообщение не дало вам точного ответа, который вы искали. У всех нас разные ситуации, и невозможно учесть каждую конкретную потребность, которая может возникнуть. Вот почему я хочу поделиться с вами: Мое руководство по быстрому решению ваших проблем! В этой статье я рассказываю о лучших стратегиях, которые я придумал за эти годы, чтобы получить быстрые ответы на сложные проблемы в Excel, PowerPoint, VBA, вы называете это!
Я настоятельно рекомендую вам ознакомиться с этим руководством, прежде чем просить меня или кого-либо еще в разделе комментариев решить вашу конкретную проблему. Я могу гарантировать, что в 9 случаях из 10 одна из моих стратегий даст вам ответы, которые вам нужны, быстрее, чем мне потребуется, чтобы вернуться к вам с возможным решением. Я изо всех сил стараюсь помочь всем, но иногда у меня нет времени ответить на все вопросы (кажется, часов в сутках никогда не бывает достаточно!).
Желаю вам удачи и надеюсь, что это руководство направит вас в правильном направлении!
Вы когда-нибудь задумывались об автоматизации функции "Найти и заменить" в Excel? В VBA мы можем создать макрос, который можно использовать для поиска и замены всего, что мы обычно делаем в Excel, нажимая сочетания клавиш Ctrl + H. С помощью VBA Find and Replace мы можем автоматизировать поиск любого слова и замену его другой заменой. Это помогает, когда нам нужно выполнить одно и то же действие несколько раз.
Как найти и заменить слова в Excel VBA?
Ниже приведены различные примеры поиска и замены слов в Excel с использованием кода VBA.
Оценка, Hadoop, Excel, мобильные приложения, веб-разработка и многое другое.
В очень простом примере у нас есть набор данных, состоящий из некоторого имени. Некоторые имена повторяются, как показано ниже.
Теперь мы создадим код, по которому найдем любое повторяющееся имя и заменим его чем-то другим. Для этого выполните следующие действия.
Шаг 1. Перейдите в меню «Вставка» в VBA и выберите параметр «Модуль», как показано ниже.
Шаг 2. Как только мы это сделаем, откроется новый модуль. В этом модуле напишите подкатегорию с именем VBA Find and Replace или вы можете выбрать любое имя этой подкатегории.
Обучение VBA (3 курса, более 12 проектов) 3 онлайн-курса | 13 практических проектов | 45+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,7 (10 154 оценки)
Код:
Шаг 3. Поскольку у нас есть данные в столбце B от ячейки B2 до B10, мы сначала выберем этот диапазон из B2: B10.
Код:
Шаг 4. Теперь, чтобы что-то заменить, нам нужно найти это слово. Здесь также мы сначала выберем функцию «Заменить», чтобы посмотреть, что нам нужно заменить.
Код:
Согласно синтаксису функции замены, мы найдем то, что нужно заменить.
Шаг 5. Давайте выберем первое имя в списке «Бен» в качестве того, что нам нужно заменить.
Код:
Шаг 6: Теперь выберите заменяющее слово, которым нам нужно заменить выбранное слово. Здесь мы решили заменить имя Бен на Сэм, которое упоминается как Замена, как показано ниже.
Код:
Шаг 7. Теперь скомпилируйте код и запустите его, нажав кнопку воспроизведения или клавишу F5, как показано ниже.
Мы увидим, что все ячейки, содержащие имя Бен, теперь заменены именем Сэм. И это также выделено желтым цветом.
Каждый из нас мог столкнуться с ситуацией, когда мы пытались найти и заменить некоторые слова буквами, чувствительными к регистру. Предположим, в списке, если мы хотим заменить только то слово, которое имеет строчные или прописные буквы. И если мы используем процесс, который мы видели в примере 1, то в конечном итоге мы заменим все похожие слова в этом списке вместе с требуемым. Этот процесс нельзя выполнить с помощью функции поиска и замены (Ctrl + H) Excel. Для этого у нас есть возможность в VBA.
Давайте рассмотрим приведенные ниже данные. Как мы видим, мы намеренно добавили слово BEN в верхнем регистре в ячейку B2.
Выполните следующие действия, чтобы найти и заменить слово BEN.
Шаг 1. Запустите подкатегорию поиска и замены VBA, как показано ниже.
Код:
Шаг 2. Выберите список в диапазоне от B2 до B10.
Код:
Шаг 3: Теперь выберите точное слово, которое мы хотим заменить. Здесь мы выбрали BEN, который находится в ячейке B2.
Код:
Шаг 4: Опять же, мы выбрали то же слово, которое мы видели в примере 1, в качестве замены BEN на Sam.
Код:
Шаг 5. Теперь для замены точного слова BEN нам нужно использовать MatchCase, если оно становится ИСТИНА, как показано ниже.
Код:
Регистр MatchCase поможет нам идентифицировать слово, которое мы хотим заменить на точное соответствие. Здесь слово, которое мы выбрали, — BEN в верхнем регистре, и в списке есть и другие подобные слова, но в правильном регистре.
Шаг 6. Теперь запустите код, нажав кнопку "Воспроизвести" или нажав клавишу F5.
Мы увидим, что слово BEN, которое было в ячейке B2, теперь заменено словом «Sam», выделенным желтым цветом. А аналогичные слова Ben, которые находятся в ячейках B5 и B8 соответственно, остались без изменений.
Вот как работает замена с точным соответствием.
Шаг 7. Теперь мы удалим добавленный MatchCase из приведенного выше кода и посмотрим, как это будет работать.
Код:
Шаг 8. Снова скомпилируйте и запустите код.
Мы увидим, что код заменил все ячейки, содержащие слово Ben, как показано выше. Это означает, что после удаления MatchCase код будет работать так, как мы видели в примере 1.
Плюсы поиска и замены Excel VBA
- Основной урок заключается в том, что мы можем заменять чувствительные к регистру слова и содержимое ячеек с помощью VBA Find and Replace, как показано в примере 2.
- Если действие выполняется вручную и многократно повторяется, то автоматизация этого будет преимуществом в плане экономии времени и усилий.
- Даже если у нас есть огромный набор данных, в которых мы хотим заменить определенные слова, это можно сделать без сбоя Excel в больших файлах.
Что нужно помнить
- Мы можем создать макрос функции «Найти и заменить» с помощью параметра «Запись макроса» на вкладке «Разработчик». Это самый простой способ создать макрос, если вы новичок в программировании.
- Сохранение файла в формате Excel с поддержкой макросов позволит использовать код в будущем.
- Мы можем заменить любой текст, слово, число или символ с помощью функции поиска и замены VBA.
Рекомендуемые статьи
Это руководство по поиску и замене в VBA. Здесь мы обсуждаем, как находить и заменять слова в Excel с помощью VBA, а также приводим практические примеры и загружаемый шаблон Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –
Как создать код макроса для поиска и замены текста в Excel?
Обычно мы используем встроенную функцию «Найти и заменить» для поиска и замены значений в Excel. На самом деле, вы можете найти и заменить текст в Excel напрямую с помощью кода макроса. В этой статье вы узнаете, как создать код макроса для поиска и замены текста в Excel.
Создайте код макроса для поиска и замены текста в Excel
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Выполните следующие действия, чтобы создать код макроса для поиска и замены текста в Excel.
<р>1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений. <р>2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модуль. Затем скопируйте приведенный ниже код VBA в окно кода.Код VBA: создайте код макроса для поиска и замены текста в Excel
<р>3. Нажмите клавишу F5, чтобы запустить код. <р>4. В первом диалоговом окне Kutools for Excel введите текст или значение, которое вы будете искать, а затем нажмите OK Кнопка. Смотрите скриншот:<р>5. Во втором диалоговом окне Kutools for Excel введите текст или значение, которое вы замените, и нажмите OK кнопка.
Затем все указанные тексты немедленно заменяются нужным текстом.
Читайте также: