Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 13:44:05
Bezeichnung Tabellenblatt aus Zelle für Formel auslesen
24.04.2024 12:38:40
Formeländerung durch Wechsel von Excel 2021 auf 2019
24.04.2024 12:31:18
Excel - Sverweis mit Wenn-Abfrage kombinieren
24.04.2024 12:16:26
Name von aktuellem VBA-Modul ausgeben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1904to1908
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1904to1908
1904to1908
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchen per VBA in externer Arbeitsmappe

Suchen per VBA in externer Arbeitsmappe
27.10.2022 15:28:15
Kurt
Hallo Community,
ich bitte euch wieder einmal Händeringend um eure geschätzte Hilfe.
Was möchte ich tun und nicht per VBA schaffe:
Ich möchte aus der Arbeitsmappe "Interpreten" in der zu öffnenden externen Arbeitsmappe "Country" nach Namen suchen und markieren (siehe beigefügtes Bild).
Doch dieser sollte, wenn möglich für über 2000 Namen uni­ver­sell einsetzbar sein.
Mit dem Recorder konnte ich nicht das erwünschte Ergebnis erzielen
Ich danke euch im Voraus recht herzlich für eure Hilfe
Lg Speedy
Userbild

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
27.10.2022 15:51:43
Kurt
PS: die Suche darf nicht Zellbezogen sein, da die Möglichkeit besteht die Dateien individuell zu Sortieren.
Danke für die Mühe
Kurt
AW: Suchen per VBA in externer Arbeitsmappe
28.10.2022 09:02:20
MCO
Hallo Speedy!
Wahrscheinlich baut Dir hier keine eine Mappe zusammen, geschweige denn zwei.
Bitte Beispielmappen hochladen....
Gruß, MCO
AW: Suchen per VBA in externer Arbeitsmappe
28.10.2022 10:11:54
Kurt
Hallo Community und danke MCO für deine info.
Nun habe ich 2 Arbeitsmappen hochgeladen, wobei die Mappe "Interpret" die Hauptmappe sein soll aus der die suche gestartet werden soll. Der Suchmakro sollte auch
auf weitere Mappen zugreifen können.
https://www.herber.de/bbs/user/155909.xlsm
https://www.herber.de/bbs/user/155910.xlsm
Grüße und besten Dank an die Community
Kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 09:23:00
MCO
Hallo Kurt!
Damit klappt es bei mir.
Allerdings ändern sich ja beim Hochladen die Dateiname, daher musst den Pfad anpassen.

Sub Interpr_suche()
Dim twb  As Workbook
Dim such_wb As Workbook
Dim interpret As Range
Dim such_rng As Range
Set twb = ThisWorkbook
'Set such_wb = Workbooks.Open("Hier muss der Pfad der Datei rein")
Set such_wb = Workbooks("155910.xlsm")
Set such_rng = such_wb.Sheets(1).Range("B:B")
'Durchlauf
For Each interpret In Range("A4:A9999").SpecialCells(xlCellTypeConstants)
Set gefunden = such_rng.Find(what:=interpret, lookat:=xlWhole)
If Not gefunden Is Nothing Then
interpret.Interior.ColorIndex = 4
gefunden.Interior.ColorIndex = 4
Else
'interpret.Interior.ColorIndex = 3
End If
Next interpret
such_wb.Close 1
End Sub
Gruß, MCO
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 12:04:53
Kurt
Hallo MCO,
erstmal danke für deine Mühe.
Leider funzt er bei mir nicht. Er öfnet die Datei, markiert mir die Spalte "B" bis zum letzten Eintrag, doch er gibt mir den gesuchten Namen nicht aus.
Den Befehl "such_wb.Close 1" habe ich raus genommen da er mir die zu öffnende Datei sofort wieder schloss.
Unten abgebildet der von mir abgeänderte Makro.

Sub Interpr_suche()
Dim twb  As Workbook
Dim such_wb As Workbook
Dim interpret As Range
Dim such_rng As Range
Set twb = ThisWorkbook
'Set such_wb = Workbooks.Open("Hier muss der Pfad der Datei rein")
Set such_wb = Workbooks.Open("Z:\Musik\Titel-Interpreten\Country_Folk.xlsm")
Set such_rng = such_wb.Sheets(1).Range("B:B")
'Durchlauf
For Each interpret In Range("A4:A9999").SpecialCells(xlCellTypeConstants)
Set gefunden = such_rng.Find(what:=interpret, lookat:=xlWhole)
If Not gefunden Is Nothing Then
interpret.Interior.ColorIndex = 4
gefunden.Interior.ColorIndex = 4
Else
'interpret.Interior.ColorIndex = 3
End If
Next interpret
End Sub
Grüße Kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 12:17:12
peterk
Hallo
Da das gerade geöffnete WB das Aktive ist, musst Du ordentlich referenzieren.
For Each interpret In twb.Range("A4:A9999").SpecialCells(xlCellTypeConstants)
Peter
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 13:24:50
Kurt
Hallo Peter
Leider bekomme ich dadurch eine Fehlermeldung:
Laufzeitfehler 438
Oblekt unterstützt diese Eigenschaft oder Methode nicht.
Gruß Kurt
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 13:43:20
peterk
Hallo Kurt
"twb" ist ja ein Workbook (sorry), also muß auch noch auf das Worksheet referenziert werden.
For Each interpret In twb.worksheets("Interpreten").Range("A4:A9999").SpecialCells(xlCellTypeConstants)
Peter
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 15:36:45
Kurt
Hallo Community.
Peter Danke, jetzt läuft der Makro ohne Fehlermeldung durch.
Leider gibt er mir nicht die in der "Masterdatei" aktive Zelle (Namen) in der zu durchsuchenden Datei den den Namen zurück.
Es werden stattdessen "willkürlich" Zellen markiert. :-(
grüße Kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 16:27:19
peterk
Hallo
Das Makro sucht ALLE Einträge aus dem Worksheet "Interpret" (For Each interpret In Range("A4:A9999").SpecialCells(xlCellTypeConstants)) und markiert den ERSTEN Treffer in der neue geöffneten Arbeitsmappe (kann durchaus willkürlich aussehen). Meine Frage: Was genau ist Dein Wunschergebnis?
Peter
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 17:21:59
Kurt
peter, mein "Wunschergebnis wäre ....
Ich möchte aus der "Masterdatei" (Interpreten) die aktive Zelle in der zu öffneten Datei, in dem Fall "Z:\Musik\Titel-Interpreten\Country_Folk.xlsm" den Namen der in der aktiven Zelle der Masterdatei steht suchen und markieren. Wobei die Suche auf weitere Dateien ausdehnbar sein soll. Auch die Suche mit Zellbezug sollte ausgeschlossen sein, da die Namen in den zu durchsuchenden Dateien Sortierbar sind.
Dank für die Mühe und Grüße
Kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 17:47:41
peterk
Hallo Kurt
Für "John Denver" hast Du 82 Treffer. Wie willst Du diese markieren? Wie willst Du die Suche auslösen? (durch z.B. Doppelklick auf den Interpreten?)
Was möchtest Du damit erreichen?
Peter
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 18:21:42
Kurt
Peter, danke für deine Nachfrage.
Auszulösen bitte mit Doppelklick auf den Namen.
Makieren: nur der erstemögliche Treffer
Zweck:
Weiterleitung zu einer Datei wo dem Interpreten Titel und Musik zugeordnet sind.
Wenn ich die betreffenden Dateien zu einer Arbeitsmappe zusammen führen würde, wäre die Ladezeit hoch (ca 3 min).
Muß gestehen .....hier kommt der blutige Anfänger mit VBA zu Tage :-)
Gruß
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 19:32:41
peterk
Hallo
Hier mal ein kleines Beispiel
https://www.herber.de/bbs/user/155944.xlsm
Es sind 2 Module enthalten:
Allgemein: Führt die Suche durch und springt zur Fundstelle (hier muss der Pfad noch angepast werden)
Sheet "Interpret" : Makro das bei Doppelkick ausgelöst wird
Peter
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 20:07:24
Kurt
DANKE Peter, dieser Marko entsprich genau meinen Vorstellungen.
würde mich gerne Erkenntlich zeigen.
Meine besten Grüße
kurt
AW: Suchen per VBA in externer Arbeitsmappe
31.10.2022 21:01:34
Kurt
Peter noch eine Frage an dich
Wie kann ich diesen Makro für weiter 9 Dateien adaptieren, sprich ... ohne immer den Pfad ändern zu müssen.
Mir ist es mit meinem geringen VBA - Wissen und experimentieren nicht gelungen eine Lösung zu finden.
Gruß
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 15:10:02
Kurt
Hallo Peter, Dankeee für deine Mühe und Zeit die du für mich investiert hast.
Leider funktioniert es bei mir nicht so. Es öffnet sich zwar die Datei in der gesucht werden soll, doch kommt die Meldung "Leider nicht gefunden"
lg
Kurt
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 15:46:41
peterk
Hallo
Es wird immer im ersten Sheet gesucht (Sheets(1)). Wenn Du z.B. eine Pivot-Tabelle erzeugst wird diese immer VOR den Daten eingefügt und somit die neue Tabelle 1
Peter
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 16:48:47
Kurt
:-( Habe in deiner Musterdatei nichts verändert. Nur mit einem anderen Namen abgespeichert
Gruß Kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 17:43:25
peterk
Hallo
Erweitere die MsgBox um folgenden Teil

Else
twb.Activate
Call MsgBox(SuchText & vbLf & "Leider nicht gefunden" & vbLf & _
"Worksheet Name: " & such_wb.Sheets(1).Name & vbLf & _
"Worksheet Count: " & such_wb.Worksheets.Count, _
vbCritical, "Interpret")
End If
Der Worksheet Count sollte 1 sein, falls nicht sucht das Programm vielleicht im falschen Sheet.
Stimmen die Pfade im Sheet "Pfade"?
Peter
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 19:15:31
Kurt
Peter, jetzt gibt mir die MsgBox die Meldung
wilton Crawley
Leider nicht gefunden
Worksheet Name: Jazz
Worksheet Count: 1
zurück.
kurt
Anzeige
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 19:53:22
peterk
Hallo Kurt
Kannst Du die Jazz Datei uploaden?
Peter
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 20:06:26
peterk
Hallo Kurt
Oder es liegt an vorangestellen/nachfolgenden Leerzeichen .
Ändere die Prozedur Worksheet_BeforeDoubleClick und füge ein TRIM ein.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim UsedRange As Range
Set UsedRange = Range("A4:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If Not (Intersect(Target, UsedRange) Is Nothing) Then
Call Interpr_suche(SuchText:=Trim(Target.Value), Genre:=Target.Offset(0, 1).Value)
End If
Cancel = True
End Sub
Peter
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 20:59:49
peterk
Hallo
Der Interpret steht nun in der Spalte C (!) wir suchen aber in Spalte B (!)
Ändere folgendes:
Set such_rng = such_wb.Sheets(1).Range("B:B")
auf
Set such_rng = such_wb.Sheets(1).Range("C:C")

Sub Interpr_suche(SuchText As String, Genre As String)
Dim twb  As Workbook
Dim such_wb As Workbook
Dim gefunden As Range
Dim such_rng As Range
Dim wb_name As String
Set twb = ThisWorkbook
' Ermittlung des Filepfades
Set gefunden = twb.Worksheets("Genre").Range("A:A").Find(what:=Genre, lookat:=xlWhole)
If Not gefunden Is Nothing Then
wb_name = gefunden.Offset(0, 2).Value
If wb_name = "" Then
Call MsgBox(Genre & vbLf & "Es wurde kein Pfad gefunden", vbCritical, "Pfad")
Exit Sub
End If
Else
Call MsgBox(Genre & vbLf & "Genre wurde nicht gefunden", vbCritical, "Genre")
Exit Sub
End If
' Suche im Workbook
Set such_wb = Workbooks.Open(wb_name)
Set such_rng = such_wb.Sheets(1).Range("C:C")
Set gefunden = such_rng.Find(what:=SuchText, lookat:=xlWhole)
If Not gefunden Is Nothing Then
such_wb.Activate
gefunden.Select
Else
twb.Activate
Call MsgBox(SuchText & vbLf & "Leider nicht gefunden" & vbLf & _
"Worksheet Name: " & such_wb.Sheets(1).Name & vbLf & _
"Worksheet Count: " & such_wb.Worksheets.Count, _
vbCritical, "Interpret")
End If
End Sub
Peter
AW: Suchen per VBA in externer Arbeitsmappe
02.11.2022 21:23:10
Kurt
Peter Dankee recht herzlich Dank für deine Mühe, Zeit und Geduld, er funktioniert super
auf die Spaltenbuchstaben hab ich gar nicht geschaut. Meine Schuld :-(
Liebe Grüße und nochmal herzlichen Dank
Kurt

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige