Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1572to1576
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
Inhaltsverzeichnis

SVERWEIS

SVERWEIS
04.08.2017 15:42:03
Christoph
Hallo zusammen,
ich versuche mich immernoch an VBA.
Ich habe eine Datei mit Userkürzeln in der Spalte A und möchte nun aus einer anderen Datei den Namen des Users in die Spalte B übertragen.
Ich habe einen Sverweis zu der anderen Datei an einem anderem Speicherort erstellt.
Es funktioniert auch alles. Allerdings sollen mit einem Makro mehrere S-Verweise ausgeführt werden. Quasi automatisch alle Mitarbeiter.
Folgendes Makro habe ich geschrieben:

Sub GMTCC()
With Range("B8")
.Formula = "=VLookup(A8," & _
"'Dateiname (Datenschutz)!$A$2:$D$9596,3,False)"
.Formula = .Value
End With
End Sub

Wie kann ich die Formel spo modifizieren, dass alle Mitarbeiter zum Beispiel Spalte A:K abgefragt werden?
Danke im Voraus!

34
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVERWEIS
04.08.2017 15:57:11
ChrisL
Hi Christoph
Wo stehen die Mitarbeiter genau und wohin brauchst du das Ergebnis?
Grundsätzlich einfach den Bereich erweitern z.B. Range("B8:B20").
cu
Chris
AW: SVERWEIS
04.08.2017 16:02:46
Christoph
Danke für deine Antwort.
Das Suchkriterium steht in Spalte B (Userkürzel). Der Name des Mitarbeiters steht in der anderen Datei in Spalte C und soll in die erste Datei in Spalte D eingetragen werden.
Hilft dir das?
AW: SVERWEIS
04.08.2017 16:16:24
ChrisL
Hi
So?
Sub GMTCC()
With Range("D8:D" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=VLookup(B8," & _
"'Dateiname (Datenschutz)'!$A$2:$D$9596,3,False)"
.Value = .Value
End With
End Sub

cu
Chris
Anzeige
AW: SVERWEIS
04.08.2017 16:19:39
Christoph
Genau so. Besten Dank. Kann man es irgendwie erreichen, dass in den Zellen wo der S Verweis kein Suchkriterium hat nicht "N/V" erscheint?
AW: SVERWEIS
04.08.2017 16:26:02
ChrisL
Einfach die Formel anpassen...
"=IF(B8="""","""",VLookup(B8,'Dateiname (Datenschutz)'!$A$2:$D$9596,3,False))"
cu
Chris
AW: SVERWEIS
04.08.2017 16:24:46
Christoph
bzw. kann man VBA auch sagen, dass er in 2 Datein suchen soll?
AW: SVERWEIS
04.08.2017 16:28:08
ChrisL
Ja, bastle weiter an der Formel. Sinngemäss
=WENN(ersterSVERWEIS="";zweiterSVERWEIS)
cu
Chris
AW: SVERWEIS
04.08.2017 16:40:45
Christoph
Puh. Das ist mir dann doch etwas zu schwer........... Wie gesagt ich bin noch neu bei VBA...
AW: SVERWEIS
04.08.2017 16:44:19
ChrisL
Es geht ja nur um die Formel, die ist dann schnell übersetzt...
Anzeige
AW: SVERWEIS
04.08.2017 16:45:58
Christoph
Ja allerdings bin ich erst noch dabei die Grundlagen zu erlernen.
Wenn ich nun meine Formel nehme hört es bei mir leider schon auf, wo ich mit dem IF anfangen soll, damit VBA es korrekt erkennt und umsetzt.
Sub GMTCC()
With Range("D4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=VLookup(B4," & _
"'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False)"
.Value = .Value
End With
End Sub

AW: SVERWEIS
04.08.2017 16:48:43
ChrisL
Setze die Formel einfach mal manuell auf deutsch ein...
Zum übersetzen kannst dich ja wieder melden, aber ist ganz einfach
WENN = IF
SVERWEIS = VLOOKUP
; = ,
Anzeige
AW: SVERWEIS
04.08.2017 16:54:58
Christoph
Ist das schon die Richtung oder komplett falsch?
Sub GMTCC()
With Range("D4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
=IF(VLookup(B4,"'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3, _
False="";VLookup(B4,"'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False;"")"
.Value = .Value
End With
End Sub

AW: SVERWEIS
04.08.2017 17:05:36
ChrisL
Kleiner Salat mit den Anführungszeichen/Klammern und Strichpunkt nicht konsequent durch Komma ersetzt, aber sonst auf den ersten Blick OK
.Formula = "=IF(VLookup(B4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False)=0,VLookup(B4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False),"""")"
cu
Chris
Anzeige
AW: SVERWEIS
04.08.2017 17:11:56
Christoph
Danke.
Habe das Makro jetzt wie folgt aufgesetzt:
Sub GMTCC()
With Range("D4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=IF(VLookup(B4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3, _
False)=0,VLookup(B4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False),"""")"
.Value = .Value
End With
End Sub
Leider bringt er mir jetzt bei allen Zellen #NV?!
Kannst du auf anhieb sehen woran das liegt?
AW: SVERWEIS
04.08.2017 17:13:53
ChrisL
Ja habs grad gemerkt, der Formelaufbau ist noch fehlerhaft... (Bereiche und Bezeichnungen noch anpassen)
=WENN(ISTFEHLER(SVERWEIS(B8;'Dateiname (Datenschutz)'!A$2:C$3;3;FALSCH));SVERWEIS(B8; Tabelle3!A$2:C$4;3;FALSCH);SVERWEIS(B8;'Dateiname (Datenschutz)'!A$2:C$3;3;FALSCH))
.Formula = "=IF(ISERROR(VLOOKUP(B8,'Dateiname (Datenschutz)'!A$2:C$3,3,FALSE)),VLOOKUP(B8,Tabelle3!A$2:C$4,3,FALSE),VLOOKUP(B8,'Dateiname (Datenschutz)'!A$2:C$3,3,FALSE))"
cu
Chris
Anzeige
AW: SVERWEIS
04.08.2017 17:20:36
Christoph
habe das jetzt so abgeändert:
WENN Sverweis.... = Fehler Dann Sverweis... Sonst ""
Leider bekomme ich jetzt Fehler 400....
Sub GMTCC()
With Range("D4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=IF(ISERROR(VLOOKUP(B4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$ _
15000,3,False)),VLookup(B4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False),""))"
.Value = .Value
End With
End Sub

Letztes Argument 4 statt nur 2 "! Gruß owT
04.08.2017 19:05:39
Luc:-?
:-?
AW: SVERWEIS
04.08.2017 19:06:14
Daniel
Hi
was willst du denn konkret?
wenn der erste SVerweis einen Fehler hat, dann führe einen anderen SVerweis aus?
das ginge so:
=Wenn(IstFehler(SVerweis(A)),SVerweis(B);SVerweis(A))
oder: wenn der erste SVereis einen Fehler hat, dann gib den Leerstring aus, ansonsten das das Ergebnis des Verweises?
das ginge dann so:
=Wenn(IstFehler(SVerweis(A)),"";SVerweis(A))
wobei du in Excel 2010 die Funktion WENNFEHLER verwenden kannst, was die sache einfacher macht:
=WennFehler(SVerweis(A);SVerweis(B))

bzw für den zweiten Fall:
=WennFehler(SVerweis(A);"")

zuviel zu Formellogik.
Was in deinem zuletzt gezeigten Code formal falsch ist, sind die Anführungszeichen.
in VBA gilt die Regel, dass Anfürhrungszeichen, die zur Formel gehöhren, immer Doppelt eingegeben werden müssen (also "" statt ").
Daran kann sie der VBA-Editor dann von den Anführungszeichen unterscheiden, die den Teilstring begrenzen.
Außerdem stimmen bei dir die Klammern nicht, es müssen immer genau so viele "(" wie ")" sein.
in deinem Fall konkret:
du hast aber 4x "(" und 5x ")", das kann nicht stimmen.
ich gehe mal davon aus, dass du die Variante meinst, "WennFehler, dann Leerstring.
dazu müsste die Formel so aussehen:
.Formula = "=IF(ISERROR(VLOOKUP(B4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False)),"""",VLookup(B4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,3,False))"
Anzeige
AW: SVERWEIS
07.08.2017 11:29:37
Christoph
Hallo, genauso meinte ich die Formel, besten Dank!
AW: SVERWEIS
07.08.2017 14:01:48
Christoph
Sorry, ich muss mich noch einmal melden, nachdem ich das Szenario geprüft habe.
Meine Formel sollte eher folgendes beiinhalten:
Wennfehler Sverweis A, Sverweis B, ""
Ich habe die oben zu sehende Formel wie folgt umgestellt doch leider funktioniert das nicht. Was ist daran falsch?
Sub GMTCC()
With Range("C4:D" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=IF(ISERROR(VLOOKUP(A4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$ _
15000,2,False)),VLookup(A4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,2,False))"""""
.Value = .Value
End With
End Sub

Danke für die Hilfe!
Anzeige
AW: SVERWEIS
07.08.2017 14:10:24
ChrisL
hi Christoph
.Formula = "=IF(ISERROR(VLOOKUP(A4,'[Datei GB.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,2,False)),VLookup(A4,'[Datei GB1.xlsx]171_HP_MS_MS5_D20170731_Shared_'!$B$2:$L$15000,2,False),"""")"
cu
Chris
AW: SVERWEIS
07.08.2017 14:31:00
Christoph
Danke für deine Antwort. So bekomme ich allerdings gar keine Daten angezeit. Die Zellen bleiben leer.
AW: SVERWEIS
07.08.2017 14:33:38
Christoph
bzw. jetzt zieht er nur die Daten aus der 2. Datei und keine mehr aus der 1.
AW: SVERWEIS
07.08.2017 14:46:52
ChrisL
lass einfach mal folgende Zeile weg:
.Value = .Value
So kannst du die Formel anschauen und einfach prüfen.
AW: SVERWEIS
07.08.2017 14:54:13
Christoph
Meiner Meinung nach ist die Formel korrekt. Wenn Sverweis A = error, Sverweis B, sonst leer
Trotzdem erhalte ich wieder in den Zellen #N/V :(
Anzeige
AW: SVERWEIS
07.08.2017 15:32:39
ChrisL
Es braucht noch eine WENN mehr...
=WENNFEHLER(WENN(ISTFEHLER(SVERWEIS(A4;'171_HP_MS_MS5_D20170731_SharedA'!$B$2:$L$15000;2;FALSCH)); SVERWEIS(A4;'171_HP_MS_MS5_D20170731_SharedB'!$B$2:$L$15000;2;FALSCH);SVERWEIS(A4; '171_HP_MS_MS5_D20170731_SharedA'!$B$2:$L$15000;2;FALSCH));"")
.Formula = "=IFERROR(IF(ISERROR(VLOOKUP(A4,'171_HP_MS_MS5_D20170731_SharedA'!$B$2:$L$15000,2,FALSE)),VLOOKUP(A4,'171_HP_MS_MS5_D20170731_SharedB'!$B$2:$L$15000,2,FALSE),VLOOKUP(A4,'171_HP_MS_MS5_D20170731_SharedA'!$B$2:$L$15000,2,FALSE)),"""")"
cu
Chris
AW: SVERWEIS
07.08.2017 16:16:12
Christoph
Perfekt. Da wäre ich selbst nicht drauf gekommen. Vielen Dank.
Darf man fragen, woher du dich so gut in VBA auskennst?
LG
Anzeige
AW: SVERWEIS
07.08.2017 16:57:14
ChrisL
Bitte...
Das war "Learning by Doing". Hobbymässig eigene Projekte gebastelt und viel im Forum mitgelesen resp. versucht selber eine Lösung auf Forumsfragen zu finden. Ursprünglich hatte ich mal mit einem Commodore 64 begonnen.
cu
Chris
AW: SVERWEIS
07.08.2017 17:04:02
Christoph
Wow. Da bin ich begeistert. Wie lange bist du jetzt ca. dabei?
AW: SVERWEIS
07.08.2017 17:13:15
ChrisL
Commodore 64 war in den 80er Jahren. Allerdings führe ich mit (V-)Basic eine On-/Off-Beziehung ;)
AW: offTopic
07.08.2017 17:17:42
Fennek
der erste Basic Befehl, den ich kennengelernt habe, war
? 1+1
? für die Ausgabe/print.
Das geht heute noch im xl-Direktfenster:
? activecell.formula
AW: offTopic
07.08.2017 17:59:45
ChrisL
Eigentlich gar nicht so OffTopic, weil
debug.print activecell.formula
eine gute Methode ist, um die Formel ins Englische zu übersetzen (z.B. zwecks Übernahme nach VBA). Hatte ich bisher gar nie dran gedacht und immer von Hand übersetzt.
Viel Spass noch mit VBA
Chris
Anmerk: Dafür verwende ich seit Jahren UDFs, ...
07.08.2017 20:53:10
Luc:-?
…Chris,
lasse das also Xl machen. Und jetzt (erst) gibt's ja auch FORMELTEXT…
🙈 🙉 🙊 🐵 Gruß, Luc :-?
Besser informiert mit …
Danke für den Hinweis owT
08.08.2017 08:41:13
ChrisL
.

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige