Wenn Michael in Excel „Alle suchen“ ausführt, zeigt das Programm eine Liste aller Zellen an, die alles enthalten, wonach er sucht.

Michael möchte diese Liste der Zellenadressen in ein anderes Arbeitsblatt kopieren und fragt sich, ob es eine Möglichkeit gibt, die Liste in die Zwischenablage zu kopieren, damit er sie in ein Arbeitsblatt einfügen kann.

Es gibt einige Möglichkeiten, wie Sie diese Aufgabe ausführen können, und die meisten davon beinhalten die Verwendung von Makros. Bevor wir jedoch zu den makrobasierten Ansätzen kommen, schauen wir uns an, wie Sie mit benannten Bereichen und dem Namensmanager auf die Adressen zugreifen können:

  1. Verwenden Sie FindAll wie zuvor, schließen Sie jedoch nicht das Dialogfeld Suchen und Ersetzen.

  2. Scrollen Sie in der Liste der angezeigten Adressen nach unten, halten Sie die Umschalttaste gedrückt und klicken Sie auf die letzte Übereinstimmung. Excel wählt alle übereinstimmenden Zellen aus.

  3. Drücken Sie die Esc-Taste, um das Dialogfeld Suchen und Ersetzen zu schließen. Die übereinstimmenden Zellen sind weiterhin alle ausgewählt.

  4. Geben Sie einen Namen in das Namensfeld ein (links neben der Formelleiste und direkt über Zelle A1). Dadurch wird ein benannter Bereich erstellt, der aus allen ausgewählten Zellen besteht.

  5. Zeigen Sie die Registerkarte Formeln des Menübands an.

  6. Klicken Sie auf das Namensverwaltungswerkzeug. Excel zeigt das Dialogfeld des Namensmanagers an. (Siehe Abbildung 1.)

  7. Klicken Sie auf den Namen, den Sie in Schritt 4 erstellt haben.

  8. Die Liste der Zellen befindet sich im Feld Verweise auf am unteren Rand des Dialogfelds.

Zu diesem Zeitpunkt können Sie die Informationen in das Feld Verweise auf kopieren und in das gewünschte Format einfügen (einschließlich eines anderen Arbeitsblatts). Sie müssen die Daten nach dem Einfügen ein wenig massieren, da die Liste genau das ist – eine serielle Liste von Zellenadressen.

Dies wirkt sich natürlich auf Ihre Arbeitsmappe aus, da ein benannter Bereich erstellt wird. Wenn Sie dies mehrmals tun, werden mehrere benannte Bereiche erstellt.

Dies kann natürlich schnell unhandlich werden, wenn Sie die Aufgabe häufig ausführen müssen. Hier kommen die Makrolösungen ins Spiel. Das folgende Beispiel zeigt ein Makro, das nach einem bestimmten Wert sucht und dann die Adresse jeder Zelle, die diesen Wert enthält, in ein anderes Arbeitsblatt einfügt.

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

Das Makro macht einige Annahmen. Zunächst wird davon ausgegangen, dass Sie im Arbeitsblatt mit dem Namen Sheet1 nach Informationen suchen. Zweitens wird davon ausgegangen, dass Sie die Liste der Adressen im Arbeitsblatt mit dem Namen Sheet2 platzieren möchten. Schließlich wird davon ausgegangen, dass Sie in Sheet1 nach dem Wert „qrs“ suchen. Alle diese Elemente des Makros können bei Bedarf geändert werden.

Betrachten Sie für etwas etwas flexibleres das folgende Makro. Es wird davon ausgegangen, dass Sie bereits alle Zellen ausgewählt haben, die den gewünschten Wert enthalten. (Mit anderen Worten, Sie müssen die Schritte 1 bis 3 der Schritte am Anfang dieses Tipps ausführen.) Anschließend können Sie das Makro ausführen.

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

Das Ergebnis ist, dass die Adressen der ausgewählten Zellen in das Arbeitsblatt Sheet2 eingefügt werden. Dieses Makro ist etwas flexibler, da Sie damit in jedem Arbeitsblatt alles finden können. Der einzige Teil „fest codiert“

ist das Arbeitsblatt (Sheet2), in das die Adressen eingefügt werden.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13581) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.