Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1708to1712
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
1708to1712
1708to1712
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

String Funktion in Kontext mit SVerweis

String Funktion in Kontext mit SVerweis
22.08.2019 13:58:53
Maria
Hallo zusammen,
ich habe folgendes Problem. Ich habe mehrere Ausgangsdateien, deren Daten in eine Ziel-Datei als Liste immer in eine neue Zeile eingefügt werden sollen.
Die Daten an sich werden über mehrere S-Verweis aus verschiedenen Tabellenblättern in die Liste eingetragen. Ich habe den Code dafür werden. Aber jetzt möchte ich die Daten aus anderen Tabellen, die genau wie der Ursprung aussehen, ebenfalls in die Liste eintragen, aber natürlich wieder in eine neue Zeile, da es dann weitere Proben sind. (In der Beispieldatei ist es gelb markiert, wie es dann später aussehen soll).
Mein Code so far: 

Sub Simulation()
' Simulation Makro
'probennamen eingeben
Windows("Ziel.xlsx").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C3"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B4"), Type:=xlFillDefault
Range("B2:B4").Select
Range("B3").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C4"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C5"
'Probendaten1 eingeben für 3 Proben gleichzeitig aus 1 Datei
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C3,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
Range("C2").Select
Windows("Ziel.xlsx").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C3,[Ursprung.xlsx]Tabelle2!R2C1:R5C5,3,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C4"), Type:=xlFillDefault
Range("C2:C4").Select
Range("C2").Select
Windows("Ziel.xlsx").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:F2"), Type:=xlFillDefault
Range("C2:F2").Select
' Zweite daten eingebn
Windows("Ziel.xlsx").Activate
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault
Range("D2:D4").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("C2:D2"), Type:=xlFillDefault
Range("C2:D2").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C4"), Type:=xlFillDefault
Range("C2:C4").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,4,FALSE)"
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,5,FALSE)"
Range("C2:C4").Select
Selection.AutoFill Destination:=Range("C2:F4"), Type:=xlFillDefault
Range("C2:F4").Select
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,3,FALSE)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,3,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H4"), Type:=xlFillDefault
Range("H2:H4").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,4,FALSE)"
Range("H4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,5,FALSE)"
Range("H2:H4").Select
Selection.AutoFill Destination:=Range("H2:K4"), Type:=xlFillDefault
Range("H2:K4").Select
Columns("K:K").Select
Selection.ClearContents
End Sub

Ursprungsdatei: https://www.herber.de/bbs/user/131583.xlsx
https://www.herber.de/bbs/user/131584.xlsx
Vielen Dank im Voraus
Maria

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: String Funktion in Kontext mit SVerweis
22.08.2019 14:37:03
Daniel
eine Bitte.
damit dein Code lesbar wird, entferne alle unnötigen Selects und Selections aus dem Quelltext.
dein Code wird dadurch nicht nur kürzer und besser lesbar, sondern auch schneller in der Ausführung.
beispielsweise lässt sich das hier 
    Windows("Ziel.xlsx").Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C3"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B4"), Type:=xlFillDefault
Range("B2:B4").Select
Range("B3").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C4"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C5"
so zusammenfassen:
    Worsheets("Ziel.xlsx").Activate
Range("B2").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C3"
Range("B3").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C4"
Range("B4").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C5"
bitte deinen Code mal dahingehend überarbeiten, dann kann man sich ihn auch mal anschauen.
Weitere Infos dazu hier:
https://www.online-excel.de/excel/singsel_vba.php?f=78
Gruß Daniel
Anzeige
AW: String Funktion in Kontext mit SVerweis
22.08.2019 16:15:07
Maria
Hi Daniel,
ich hab ihn verkürzt, aber hauptsächlich geht es mir ja um den Code davor, ich denke es muss eine String Funktion sein...
Danke Maria
Sub Simulation()
' Simulation Makro
'probennamen eingeben
Windows("Ziel.xlsx").Activate
Range("B2").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C3"
Range("B3").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C4"
Range("B4").FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C5"
'Probendaten1 eingeben für 3 Proben gleichzeitig aus 1 Datei
Range("C2").FormulaR1C1 = _
"=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
Range("C2").AutoFill Destination:=Range("C2:F2"), Type:=xlFillDefault
' Zweite daten eingebn
Range("C2").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
Range("D2").AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault
Range("D2").AutoFill Destination:=Range("C2:D2"), Type:=xlFillDefault
Range("C2").AutoFill Destination:=Range("C2:C4"), Type:=xlFillDefault
Range("C3").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,4,FALSE)"
Range("C4").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,5,FALSE)"
Range("C2:C4").AutoFill Destination:=Range("C2:F4"), Type:=xlFillDefault
Range("H2").FormulaR1C1 = _
"=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,3,FALSE)"
Range("H2").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,3,FALSE)"
Range("H2").AutoFill Destination:=Range("H2:H4"), Type:=xlFillDefault
Range("H3").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,4,FALSE)"
Range("H4").FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle1!R1C1:R4C5,5,FALSE)"
Range("H2:H4").AutoFill Destination:=Range("H2:K4"), Type:=xlFillDefault
Columns("K:K").ClearContents
End Sub

Anzeige
AW: String Funktion in Kontext mit SVerweis
22.08.2019 17:47:20
Daniel
Hi
erstmal noch ein kleiner Tip:
das Autofill braucht man in der Regel auch nicht in VBA.
man kann die Formel auch gleich in alle Zellen gleichzeitig schreiben.
dh wenn du den Bereich C2:C4 mit einer Formel füllen willst, reicht, ohne Autofill: 
Range("C2:C4").FormulaR1C1 = "=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"

das funktioniert auch über mehrere Spalten, wenn sich die Formel mit Copy-Paste übertragen lässt: 
Range("C2:F4").FormulaR1C1 = "=VLOOKUP(R[-1]C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"

zu deiner Frage:
du willst ja, dass du den Code mehrfach laufen lassen kannst um dann Werte aus anderen Dateien hinzuzufügen.
dann gehe so vor:
Schreibe den Anfang des Makros so
Sub Simulation()
' Simulation Makro
Dim Zelle as Range
'probennamen eingeben
Windows("Ziel.xlsx").Activate
Set Zelle = Cells(Rows.Count, 2).End(xlup).Offset(1, 0)
Zelle ist jetzt eine Range-Variable und enthält die erste freie Zelle am Tabellenende
In deinem Code entspricht sie der Zelle B2.
du beschreibst dann alle Zellen ausgehend von dieser Zelle mit .Offset.
Range("B2") wäre Zelle.Offset(0, 0) (gleiche Zeile, gleiche Spalte)
Range("C2") wäre Zelle.Offset(0, 1) (gleiche Zeile, eine Spalte nach rechts
Range("B3") wäre Zelle.Offset(1, 0) (eine Zeile nach unten, gleiche Spalte)
Range("H4") wäre Zelle.Offset(2, 6) (zwei Zeilen nach unten, 6 Spalten nach rechts.
für Zellbereiche dann so:
Range("B2:B4") wäre Range(Zelle.Offset(0, 0), Zelle.Offset(2, 0))
nach diesem Schema musst du dann deinen Code umarbeiten.
Zelle.Offset(0, 0).FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C3"
Zelle.Offset(1, 0).FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C4"
Zelle.Offset(2, 0).FormulaR1C1 = "=[Ursprung.xlsx]Tabelle2!R1C5"
Range(Zelle.Offset(0, 1), Zelle.Offset(2, 4)).FormulaR1C1 = _
"=VLOOKUP(R1C,[Ursprung.xlsx]Tabelle2!R1C1:R5C5,3,FALSE)"
naja und so weiter halt für den Rest des Codes.
wenn du auch den Datinamen schnell ändern willst, solltest du ebenfalls über eine Variable gehen.
zuerst schreibst du den Dateinamen in eine Variable: 
Sub Simulation()
' Simulation Makro
Dim Zelle as Range
Dim Datei as String
Datei = "[Ursprung.xlsx]"
und in den Formeln verwendest du die Variable dann so: 
Zelle.Offset(0, 0).FormulaR1C1 = "=" & Datei & "Tabelle2!R1C3"

dh da wo die Variable eingefügt werden soll, den Text mit Anführungszeichen beenden, die Variable mit & verketten und dann den Text wieder fortführen (zu Beginn des Textes Anführungszeichen)
Gruß Daniel
Anzeige
AW: String Funktion in Kontext mit SVerweis
23.08.2019 16:26:54
Maria
Hallo Daniel,
vielen Dank. Den ersten Teil habe ich umsetzten können, beim zweiten Teil habe ich Probleme, da ich den Code ja aus verschiedenen Dateien starten will.
Datei = "[Ursprung.xlsx]"
und in den Formeln verwendest du die Variable dann so:
Zelle.Offset(0, 0).FormulaR1C1 = "=" & Datei & "Tabelle2!R1C3"
Soll ich den Code wortwörtlich wie dort schreiben? es gibt immer eine Fehlermeldung. Oder kann man auch was mit ActiveWorkbook machen?
Danke und viele Grüße
Maria
AW: String Funktion in Kontext mit SVerweis
23.08.2019 16:51:38
Daniel
du kannst auch immer die aktuelle Datei verwenden 
Datei = "[" & ActiveWorkbook.Name & "]"
du musst halt darauf achten, dass eine korrekte Formel entsteht.
zu beachten wäre auch, dass je nach verwendeten Sonderzeichen in Datei- oder Tabellenblattname (und da gehört schon das Leerzeichen hinzu), der ganze Ausdruck von Dateiname bis vor das Ausrufezeichen in Hochkommas gesetzt werden muss.
Da das Hochkomma nicht stört, wenn man es nicht braucht, sollte man immer damit arbeiten, wenn in der Formel Blatt- oder Dateiname variabel ist.
Zelle.Offset(0, 0).FormulaR1C1 = "='" & Datei & "Tabelle2'!R1C3"

Gruß Daniel
Anzeige
AW: String Funktion in Kontext mit SVerweis
23.08.2019 18:27:34
Maria
Sorry, ich steh wohl total auf dem Schlauch...
ich habe es so eingegeben: 
Datei = "[" & ActiveWorkbook.Name & "]"
'EINFÜGEN DES REZEPTNAMENS
Zelle.Offset(0, 0).FormulaR1C1 = "='"&Datei&"Normalized Breakdown'!R1C3"

Ich bekomme aber den Fehler: Fehler beim Kompilieren: Erwartet: Anweisungsende
Was mach ich falsch, ich Habe mit Anführungszeichen und Hochkomma gepielt, aber nix tut sich.
Gruß
MAria
Spiel mal mit den vergessenen & Leerstellen (owT)
23.08.2019 18:30:54
EtoPHG

AW: String Funktion in Kontext mit SVerweis
23.08.2019 19:10:33
Daniel
Hi
vor und nach dem & muss immer ein Leerzeichen stehen!
VBA verwendet das & auch noch für andere Funktionen (z.B. voranstellt zum kennzeichnen einer Hex-Zahl)
daher muss dass Leerzeichen stehen, damit die Funktion als Verkettensymbol eindeutig ist.
(das sollte aber bekannt sein.)
Gruß Daniel
Anzeige
Genau genommen: & direkt vor einer Zahl
24.08.2019 18:44:41
Luc:-?
Dieses einzelne & hätte die Bedeutung von &O bzw &o (in VBA wird das schon beim Eintrag lt Norm gewandelt), Daniel,
würde also eine OktalZahl, die stets nur die Ziffern von 0…7 verwendet, kennzeichnen (im DirektBereich wird auch ohne o/O oktal interpretiert, nicht aber hexadezimal ohne h/H!). &8 und &9 würde so zu einem Fehler führen. Das passiert aber auch schon bei 7&7, weil hier der Operator & nicht als Verkettung erkannt wird. Eine HexadezimalZahl muss dagg zwingend mit &H (bzw &h, wird analog gewandelt) beginnen.
Wohl aus diesem Grund schiebt der VBE für Operatoren typische Leerzeichen idR nur zwischen & und folgendem bzw vorangehendem " (als TextKennzeichen) ein.
Gruß, Luc :-?
„Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen.“ H.Lesch, 2018, Sonneberg
Deshalb Intelligenz steigern mit …
Anzeige

313 Forumthreads zu ähnlichen Themen

SVERWEIS mit Text funktioniert nicht
sascha76er

Hallo,
komme einfach nicht weiter bei meinem Sverweis, vielleicht kann sich jemand mal meine Beispieldatei angucken?
Beispiel: https://www.herber.de/bbs/user/56594.xls
Vorab vielen Dank
Sascha
07.11.2008 13:38:00
Thread-ID: 1022656
5 Beiträge
Anzeige
Sverweis mit VBA funktionier nicht richtig
F.

Hallo zusammen
Ich möchte zu einer Ziffer, welche ab dem zweiten Blatt in A1 steht, die dazugehörige Bezeichnung aus dem ersten Blatt "Konti" (Spalte B) ziehen und diese dann im Feld B1 eintragen. Irgendwas mache ich falsch.
Sub Konten_bezeichnen() Dim i As Long For i = Sheets("Konti")...
10.03.2010 14:58:20
Thread-ID: 1144879
5 Beiträge
Datum von rechts nach links
rechts

Guten morgen,
ich habe folgendes Problem. Ich möchte ein Datum 11.12.2010 in folgendes Format umwandeln: 20101211 (JJJJMMTT ohne Punkte). Kann mir jemand dabei helfen?
Viele Grüße und Danke
Rüdiger
22.11.2010 11:06:56
Thread-ID: 1188121
7 Beiträge
Anzeige
Wenn, mit links und anzahl kombinieren?
Sascha

Hallo Freunde,
mein Problem: Ich habe zb. eine Spalte : D und moechte in die Spalte C: die ersten 2 beiden zahlen/zeichen haben und in die Spalte E: von rechts aus die 3.
Das geht ja mit zb. =LINKS(D2;2) und =RECHTS(D2;3).
Nun sind die Zahlen in der Spalte aber leider nicht alle 5 s...
14.02.2011 09:48:37
Thread-ID: 1200630
4 Beiträge
Wenn Funktion in Verbindung mit Links-Funktion
kathy

Hallo Leute,
ich habe Werte die wie folgt aussehen, z.B.:
1-242;1-440.9;1-632;3-200;3-990 oder
5-399.5;5-399.5:R;8-701;8-771
Jetzt benötige ich nur die erste Ziffer. Wenn nach der fünften Stelle ein ";" dann nur die ersten fünf Stellen und wenn ein "." erscheint, dann die erste...
17.03.2011 13:51:42
Thread-ID: 1206129
6 Beiträge
VLookup nach links
Horst

Hallo Excel-Freunde,
eine Frage, die ihr sicher schnell beantworten könnt: Mit folgendem Befehl suche ich per VBA mit dem SVerweis ausgehend von Spalte A (Datumsspalte) nach rechts, um dort den Wert in der 40. Spalte ausgeben zu lassen:
"VLOOKUP(today(),'C:\Dokumente und Einstellungen\U...
01.06.2011 23:24:14
Thread-ID: 1217120
12 Beiträge
Wenn Funktion gekoppelt mit Links Funktion
kathy

Hallo Leute,
ich habe zwei verschiedene Zahlenformate (entweder 7 oder 8 stellig), z.B.
- 5-690.1
- 5-790.1C
Wie müsste die Formel aussehen, wenn man sagen möchte,
wenn(zahl 7stellig; dann links 3 zahlenwerte und wenn 8stellig dann links 4 zahlenwerte)
Vielen Dank für Eu...
10.01.2012 23:28:11
Thread-ID: 1245564
4 Beiträge
SVERWEIS in anderer Tabelle nur gültige Werte
Christoph

Liebe Leute,
bitte um Unterstützung bei folgender Fragestellung:
Aus einer Tabelle mit Stamdaten (60 Spalten, ca. 500 Zeilen) wird mittels SVERWEIS
=SVERWEIS("Argument Spalte A";Stammdaten!$A2:$BH2;Suchspalte;FALSCH)
nach unterschiedlichen Argumenten in der Spalte A gesucht und...
13.01.2012 11:41:23
Thread-ID: 1246003
7 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
4
6
6
3
5
11
3
6
11
5
8
8
1
2
3
6
1
7
1
2
2
2
5