Suchen eines Arbeitsblatts mit einem bestimmten Wert in einer bestimmten Zelle (Microsoft Excel)
Kelly hat eine Arbeitsmappe, die Hunderte von Arbeitsblättern enthält. Jedes Arbeitsblatt repräsentiert ein individuelles Projekt für ihr Unternehmen. Zelle C4 jedes Arbeitsblatts enthält die Projektnummer für das jeweilige Projekt.
Das Finden einzelner Projekte aus den Hunderten in der Arbeitsmappe ist bestenfalls umständlich. Sie fragt sich, ob ein Makro erstellt werden könnte, mit dem alle Benutzer eine Jobnummer eingeben, das Arbeitsblatt mit dieser Jobnummer in Zelle C4 finden und dann dieses Arbeitsblatt anzeigen können.
Bevor Sie sich mit der Vorgehensweise in einem Makro befassen, sollten Sie wissen, dass Sie das gewünschte Projekt möglicherweise mit den nativen Tools von Excel finden können.
Sie müssen lediglich die folgenden Schritte ausführen:
-
Drücken Sie Strg + F. Excel zeigt die Registerkarte Suchen des Dialogfelds Suchen und Ersetzen an.
-
Klicken Sie auf die Schaltfläche Optionen, um das Dialogfeld zu erweitern. (Siehe Abbildung 1.)
-
Wählen Sie in der Dropdown-Liste Innerhalb die Option Arbeitsmappe.
-
Wenn die Zelle C4 in jedem Arbeitsblatt nur die Projektnummer enthält, aktivieren Sie das Kontrollkästchen Gesamten Zelleninhalt abgleichen.
-
Klicken Sie auf Weiter suchen.
Das ist es; Sie sollten zur ersten Instanz der Zelle weitergeleitet werden, die Ihre Projektnummer enthält. Wenn Ihre Arbeitsmappe Projektnummern in mehreren Zellen enthält (neben Zelle C4), kann die Verwendung des Suchwerkzeugs etwas mühsam sein. Sie müssen so lange auf Weiter suchen klicken, bis Sie das richtige Arbeitsblatt gefunden haben. In einer solchen Situation ist ein Makro möglicherweise die beste Lösung, da Sie damit die Zelle C4 genau betrachten und alles in jeder anderen Zelle ignorieren können.
Das folgende Makro fordert den Benutzer auf, eine Projektnummer einzugeben. Anschließend durchsucht es jedes Arbeitsblatt in der Arbeitsmappe nach dem ersten Arbeitsblatt, in dem Zelle C4 diese Projektnummer enthält. Wenn gefunden, zeigt das Makro das Arbeitsblatt an und aktiviert die Zelle. Wenn nicht gefunden, wird eine Meldung angezeigt, die dies anzeigt.
Sub FindProject1() Dim wks As Worksheet Dim sCell As String Dim sProj As String sCell = "C4" 'cell with Project number sProj = InputBox("What Project are you looking for?") For Each wks In Worksheets If wks.Range(sCell) = sProj Then wks.Activate wks.Range(sCell).Activate MsgBox ("Project '" & sProj & "' is in:" & vbCrLf & wks.Name) Exit Sub End If Next wks MsgBox ("Project is not found") End Sub
Es gibt zwei mögliche Probleme mit diesem Makro. Wenn Ihre Projektnummer tatsächlich eine Nummer ist, findet das Makro sie möglicherweise nicht, da es nach Textzeichenfolgen sucht. Zweitens, wenn es mehrere Arbeitsblätter gibt, die dieselbe Projektnummer verwenden, werden Sie es nie erfahren, da das Makro immer am Arbeitsblatt mit der ersten übereinstimmenden Projektnummer in Zelle C4 stoppt. Sie können diese beiden Probleme lösen, indem Sie einige Änderungen am Makro vornehmen.
Sub FindProject2() Dim wks As Worksheet Dim sCell As String Dim sProj As String Dim vSheets As New Collection Dim sTemp As String sCell = "C4" 'cell with Project number sProj = InputBox("What Project are you looking for?") For Each wks In Worksheets If wks.Range(sCell).Value = sProj Or _ wks.Range(sCell).Value = Val(sProj) Or _ wks.Range(sCell).Text = sProj Then vSheets.Add wks End If Next wks Select Case vSheets.Count Case 0 sTemp = "Project " & sProj & " was not found " sTemp = sTemp & "in this workbook." MsgBox sTemp Case 1 wks.Activate wks.Range(sCell).Activate Case Else sTemp = "Project " & sProj & " was found on more " sTemp = sTemp & "than one sheet:" & vbCrLf For Each wks In vSheets sTemp = sTemp & wks.Name & vbCrLf Next wks MsgBox sTemp End Select End Sub
Bei dieser Version des Makros wird das Projektarbeitsblatt nur angezeigt, wenn die Projektnummer in allen Arbeitsblättern eindeutig ist. Wenn es mehrere Fälle gibt, in denen die Projektnummer identisch ist, werden Sie über den Namen dieser Arbeitsblätter informiert, es wird jedoch kein Arbeitsblatt ausgewählt.
Falls gewünscht, können Sie das Makro viel einfacher gestalten, indem Sie alle Ihre Arbeitsblätter in die Projektnummer umbenennen. Dies hätte auch den zusätzlichen Vorteil, dass sichergestellt wird, dass kein Projekt tatsächlich zwei Arbeitsblätter verwendet. (Alle Arbeitsblattnamen müssen eindeutig sein. Wenn also jedes Arbeitsblatt eine Projektnummer als Namen verwendet, können Sie nur ein Arbeitsblatt pro Projektnummer verwenden.) Wenn Sie diesen Ansatz wählen, können Sie ein Makro ähnlich dem folgenden verwenden, um zu suchen und zu springen zum gewünschten Arbeitsblatt:
Sub FindProject3() On Error GoTo ErrorHandler Sheets(InputBox("Enter Project Number:")).Activate Exit Sub ErrorHandler: MsgBox "No such Project" End Sub
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (13489) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.