In Excel vba nach einem Namen suchen und (in Bezug dazu) Wert kopieren?
Hallo,
ich schreibe gerade ein neues Makro für eine Excel Tabelle und habe dabei folgendes Problem. Ich erläutere es anhand der angehängten Grafik:
In der Grafik ("Mappe 1") steht der Name in Spalte A und ein Wert in Spalte B. Ich möchte, dass Excel (per Makro, CommandButton) nach einem Namen sucht und den dazugehörigen Wert in Spalte B (selbe Zeile) kopiert und in Spalte D einfügt. Die Reihenfolge der Namen kann variieren, es soll aber immer der Wert in Spalte B in Bezug zum Namen kopiert und eingefügt werden (Beispiel: Name1 = 10€, damit sollen dann die 10€ in Spalte D:2 eingefügt werden; Name2 = 20€ und diese sollen dann in Zelle D:3 eingefügt werden) Ich habe das leider bis jetzt nur mit einzelnen Zellen hinbekommen, da die Reihenfolge der Namen allerdings pro Tabelle unterschiedlich ausfallen kann, hilft es nicht mehr ein Makro zu schreiben, dass einzelne Wert kopiert und einfügt. Kann ich das auch über einen SVERWEIS machen? Wichtig zu erwähnen wäre noch, dass die Tabelle, in der gesucht werden soll (nach dem Namen) und die Tabelle in die der Wert eingefügt werden soll (€), 2 verschiedene Worksheets sind (Excel-Dateien).
Ich bedanke mich jetzt schonmal für eurer Hilfe!
3 Antworten
verschiedene Dateien machen etwas Probleme: Die Windows(Dateiname) [ist nämlich ein Fenster-Befehl, kein Dateibefehl] müssen nämlich abwechselnd aktiviert werden (es sei denn da hätte sich grundlegend was verbessert), was zu einem unangenehmen Geflimmere und langen Laufzeiten führt oder Du schaltest auf Screenupdating = false, unbedingt aber dann mit errorhandler, der das ggf wieder auf .true setzt. Ich rate Dir deshalb, für die Zeit des Processings das Blatt einfach in Deie Datei rüberzuziehen (im Halbbild-Modus geht das recht bequem an der Blattnamensfahne) und danach wieder zurückzuschieben.
Du brauchst nicht bis zum SVerweis zu gehen, die Zeile bekommst Du auch mit Vergleich(Suchstring;Suchspalte;0) als Application.Worksheetfunction.match(what, where, how). Damit kannst du Addressierungen durchführen, da Du die Spalten ja kennst: Quelle.copy(destination:=...Range("Anderblatt!"&"D"&Funktion))
Quelle: mit ..Range("A"&obigeMatch-Funktion) .. Du kannst die gefundene Zeilennummer auch einer Variablen zuweisen.
Markierst Du einen Bereich mit vielen SuchNamen für zu übertragende Beträge, kannst Du das auch in eine Schleife einbinden:
- Sub SuchenInListeUndRüberkopieren()
- Dim C, W as integer
- For each C in selection
- W = Application.Worksheetfunction.Match(C.value, activesheet.Range("A:A"), 0)
- Range("A"&W).offset(0,1).copy(destination:= Worksheets("Anderblatt!D" & W))
- next
- end sub
ist ungetestet, ggf Syntaxfehler korrigieren lt. Hilfe (weiß zB nie, ob das destination wirklich in Klammern gehört, weil man das im Prinzip auch weglassen kann und nur das Ziel angeben, ich finds aber so besser nachvollziehbar (zB in 5 Jahren ;). Oder ob vor das Range immer noch das Activesheet. muss. Der Typ von C wird automatisch von VBA als Zelle = Range gesetzt.
Wenns nicht geht, schreibs, ich schau morgen wieder rein. Oder wenn ich Dich falsch verstanden habe.
in Deinem Beispiel tauchen die Namen mehrfach auf: Vergleich findet nur den ersten! Wenn die wirklich mehrfach mit verschiedenen Beträgen identifiziert werden sollen, reicht weder Vergleich noch SVerweis, dann muss ein etwas komplizierter find...findnext... Mechanismus her, an den ich selbst recht ungern rangehe wegen der unübersichtlichen Syntax. Dsgl, wenn die Namen in mehreren Spalten gesucht werden müssen.
Und kommentiere nochmal den Einwand von Ninombre!
Das geht mit Formeln wie Suboptimierer beschrieben hat, in VBA wäre es worksheetfunction.vlookup
Was mir nicht klar ist: Wenn Du die EUR Werte einfach nur von Spalte B nach D überträgst, weshalb muss dann nach einem Namen geschaut werden? Die Reihenfolge spielt doch auch keine Rolle, der Wert soll ja innerhalb der Zeile bleiben und ist damit in der gleichen Reihenfolge. Oder soll das nur für ausgewählte Namen passieren?
Danke für deine Frage. Die Tabelle ist normalerweise viel Größer ( ca. 800 Zellen). Es gibt eine Reihe von Auswertungen, bei denen Namen verschiedenen Zahlen zugeordnet sind. Diese Zahlen möchte ich in meine eigene Auswertung (mit den selben Namen) übernehmen. Um aber nicht jede Zelle und jede zahl einzeln kopieren zu müssen (die Tabelle ist ja wie schon gesagt etwas größer), möchte ich das gerne automatisieren.
Das müsste mit dem SVERWEIS funktionieren. Wenn die Namen in der Suchtabelle nicht sortiert sind, musst du eine genaue Suche duchführen (letzter Parameter = 0).
Bessere, aber leider etwas unübersichtlichere Alternative ist es, INDEX mit VERGLEICH zu kombinieren.
Die Funktionen arbeiten arbeitsblattübergreifend.