Когда Майкл выполняет операцию «Найти все» в Excel, программа услужливо показывает список всех ячеек, содержащих то, что он ищет.

Майкл хотел бы скопировать этот список адресов ячеек на другой лист, поэтому он задается вопросом, есть ли способ скопировать список в буфер обмена, чтобы он мог вставить его в рабочий лист.

Есть несколько способов выполнить эту задачу, и большинство из них связано с использованием макросов. Однако, прежде чем перейти к подходам на основе макросов, давайте посмотрим, как можно получить доступ к адресам с помощью именованных диапазонов и диспетчера имен:

  1. Используйте FindAll, как и раньше, но не закрывайте диалоговое окно «Найти и заменить».

  2. В отображаемом списке адресов прокрутите вниз, удерживайте клавишу Shift и щелкните последнее совпадение. Excel выберет все совпадающие ячейки.

  3. Нажмите Esc, чтобы закрыть диалоговое окно «Найти и заменить». Все совпадающие ячейки по-прежнему выбраны.

  4. Введите имя в поле «Имя» (слева от панели формул и чуть выше ячейки A1). Это создает именованный диапазон, состоящий из всех выбранных ячеек.

  5. Откройте вкладку «Формулы» на ленте.

  6. Щелкните инструмент Диспетчер имен. Excel отображает диалоговое окно диспетчера имен. (См. Рис. 1.)

  7. Щелкните имя, которое вы создали на шаге 4.

  8. Список ячеек будет в поле «Ссылается на» в нижней части диалогового окна.

На этом этапе вы можете скопировать информацию из поля «Ссылается на» и вставить ее во что угодно (включая другой рабочий лист). Вам нужно будет немного обработать данные после их вставки, поскольку список представляет собой последовательный список адресов ячеек.

Очевидно, это влияет на вашу книгу, поскольку она создает именованный диапазон. Если вы сделаете это несколько раз, у вас будет создано несколько именованных диапазонов.

Это, конечно, может быстро стать громоздким, если вам нужно выполнять задачу довольно часто. Вот здесь и вступают в игру макрорешения. Ниже приведен пример макроса, который будет искать определенное значение, а затем помещать адрес каждой ячейки, содержащей это значение, в другой рабочий лист.

Sub CellAdressList()

Dim c1 As String     Dim nxt As String

Sheets("Sheet1").Select     Range("A1").Select     Cells.Find(What:="qrs", After:=ActiveCell, _       LookIn:=xlValues, LookAt:=xlWhole, _       SearchOrder:=xlByRows, SearchDirection:=xlNext, _       MatchCase:=False, SearchFormat:=False).Activate     c1 = ActiveCell.Address     Sheets("Sheet2").Select     Range("A1").Select     Range("A1").Value = c1     Do Until nxt = c1         Sheets("Sheet1").Select         Cells.FindNext(After:=ActiveCell).Activate         nxt = ActiveCell.Address         Sheets("Sheet2").Select         ActiveCell.Offset(1, 0).Select         ActiveCell.Value = nxt     Loop     ActiveCell.Value = ""

End Sub

Макрос делает несколько предположений. Во-первых, предполагается, что вы ищете информацию на листе с именем Sheet1. Во-вторых, предполагается, что вы хотите, чтобы список адресов был помещен на лист с именем Sheet2. Наконец, предполагается, что вы ищете значение «qrs» в Sheet1. При желании все эти элементы макроса можно изменить.

Для чего-то более гибкого рассмотрите следующий макрос. Предполагается, что вы уже выбрали все ячейки, содержащие нужное вам значение. (Другими словами, вам нужно выполнить шаги с 1 по 3 из шагов, описанных в начале этого совета.) Затем вы можете запустить макрос.

Sub CopyFindAllSelection()

Dim outcell As Range     Dim c As Range

Set outcell = Range("Sheet2!A1")

For Each c In Selection         outcell.Value = c.Address         Set outcell = outcell.Offset(1, 0)

Next End Sub

В результате адреса выбранных ячеек помещаются в рабочий лист Sheet2. Этот макрос немного более гибкий, потому что он позволяет вам найти что угодно на любом листе. Единственная часть «жестко закодирована»

это рабочий лист (Sheet2), в который помещаются адреса.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13581) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.