Live-Forum - Die aktuellen Beiträge
Datum
Titel
07.05.2024 21:30:42
Bilder aus URL in Zelle einfügen - Code anpassen
07.05.2024 20:36:09
xverweis / name Funktion
07.05.2024 20:10:42
Datum z.B. 1897 wird nicht als Datum angezeigt
07.05.2024 19:47:07
Excel Lohnabrechnung anhand 2 Suchkriterien
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1896to1900
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
1896to1900
1896to1900
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVERWEIS

SVERWEIS
12.09.2022 16:54:14
Dieter
Hey all,
In einer Tabelle habe ich diese Formel verarbeitet
=WENNFEHLER(SVERWEIS(A3;Feiertag1;2;FALSCH);"")&" "&WENNFEHLER(SVERWEIS(A3;Geburtstag;2;FALSCH);"")
Die zeigt mir entweder Geburtstag, Feiertag, oder beides an. das geht auch.
Dann sind allerdings die Zellen mit der Formel belegt, heißt wenn ich noch was anderes darein schreibe ist die Formel weg.
Jetzt habe ich mir überlegt, das selbe in VBA zu machen, leider bekomme ich die obrige Formel nicht zusammen.
Das ist mein VBA, was, wie muss ich ändern damit das genauso funktioniert wie mit der Formel in jeder Zelle.
For iZeil = 3 To 70
Sheets("Kalender").Cells(iZeil, 3) _
= Application.WorksheetFunction.VLookup(Sheets("Kalender").Cells(iZeil, 1), Sheets("Feiertage").Range("B3:C30"), 2, False)
Sheets("Kalender").Cells(iZeil, 3) _
= Application.WorksheetFunction.VLookup(Sheets("Kalender").Cells(iZeil, 1), Sheets("Geburtstag").Range("A4:B30"), 2, False)
Sheets("Kalender").Cells(iZeil, 7) _
= Application.WorksheetFunction.VLookup(Sheets("Kalender").Cells(iZeil, 5), Sheets("Feiertage").Range("B3:C30"), 2, False)
Sheets("Kalender").Cells(iZeil, 7) _
= Application.WorksheetFunction.VLookup(Sheets("Kalender").Cells(iZeil, 5), Sheets("Geburtstag").Range("A4:B30"), 2, False)
usw. bis spalte 23 ( W ) geht das.
Ich danke schon mal der Hilfe.
Gruß
Dieter

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Wo ist die Verkettung?
12.09.2022 17:08:54
{Boris}
Hi,
Deine beiden Excelformeln hast Du korrekt mit & " " & verkettet - das sehr ich bei Deinem VBA-Versuch nicht...? (Ebenfalls fehlt dort die umrandende IFERROR-Abfrage bei jeder Formel).
VG, Boris
AW: Wo ist die Verkettung?
12.09.2022 17:30:33
Dieter
Hey Boris,
Deswegen wollte ich ja hier um Hilfe bitten, da ich nicht weiß wie der Code
aussehen muss damit es genauso klappt wie mit der Formel.
Wo wie kommt die Verkettung hin ?
Wo wie kommt die IFERROR-Abfrage hin ?
Leider kein Plan wie das geht.
Gruß
Dieter
Verkettung
12.09.2022 17:40:20
{Boris}
Hi,
schau Dir die Antwort von Daniel an. Hier nur so viel:
Auch in VBA wird mit & verkettet - genau wie auf Arbeitsblattebene auch.
Da die Verweisfunktionen - mit WorkSheetfunction angesprochen - nicht so ohne Weiteres Fehler verzeihen, kann man die Funktionen auch mit Application nutzen.
Hier ein abstraktes Beispiel (gesucht werden 1 mal "x" und 1 mal "y" in Spalte A und der Wert aus Spalte B wird dann jeweils ausgegeben) für die Verkettung von 2 SVERWEISen - das kann man beliebig erweitern:

Sub test()
With Application
MsgBox .IfError(.VLookup("x", Range("A:B"), 2, 0), "") & " " & .IfError(.VLookup("y", Range("A:B"), 2, 0), "")
End With
End Sub
VG, Boris
Anzeige
AW: Verkettung
13.09.2022 17:45:13
dieter
Hallo Boris,
Erst mal danke für Deine Ausführung und Erläuterung.
Ich habe es mit Deiner Sub versucht, aber leider ohne Erfolg. ( vielleicht zu alt o. dumm ?
Muss ich für das x Geburtstag eingeben ? und Y Feiertag ? dann die Spalten
MsgBox .IfError(.VLookup("Geburtstag", Range("J:K"), 2, 0), "") & " " & .IfError(.VLookup("Feiertag", Range("A:B"), 2, 0), "")
Hab es so versucht, klappt aber irgendwie nicht. Zeigt mir nur Hinweis OK
Wo habe ich den Fehler ?
Gruß
Dieter
AW: SVERWEIS
12.09.2022 17:34:11
Daniel
Hi
am einfachsten ist, du schreibst die Formel in die Zelle und ersetzt dann die Formeln durch den Inhalt.
Dann kannst du, nachdem du den Kalender erstellt hast, deine eigenen Inhalte dazu schreiben.

dim sp
for each sp in Array(3, 7)
with Sheets("Kalender").Cells(3, sp).Resize(70 - 3 + 1, 1)
.FormulaR1C1Local = "=WENNFEHLER(SVERWEIS(ZS(-2);Feiertag1;2;FALSCH);"""")&"" ""&WENNFEHLER(SVERWEIS(ZS(-2);Geburtstag;2;FALSCH);"""")
.Formula = .Value
end with
next

aber wie gesagt, beachte die Reihenfolge:
1. Kalender erstellen
2. Eigene Inhalte dazuschreiben
wenn du das Makro nochmal laufen lässt, werden eigene Inhalte in diesen Zellen überschrieben.
ein Makro, das bereits vorhandene Inhalte bestehen lässt, wäre aufwendiger.
Gruß Daniel
Anzeige
AW: SVERWEIS
13.09.2022 18:00:50
dieter
Hallo Daniel,
Vielen Dank für Deine Arbeit und Beschreibung.
Ich versteh zwar nur die Formel aber an den Rest hapert es.
Einzige was ich gemacht habe über Versuche,
for Each sp In Array(3, 7, 11, 15, 19, 23) diese Erweiterung. Spalten Sie klappt auch
Hier verstehe ich nur Bahnhof
.Cells(3, sp).Resize(70 - 3 + 1, 1)
denke irgendwas mit Spalten, Zeilen links rechts ?
Ansonsten scheint es zu funktionieren.
Versuche noch bisschen damit.
Vielen Dank
Gruß
Dieter
AW: SVERWEIS
13.09.2022 18:15:29
Daniel
Hi
das ist nur die Umsetzung von For iZeil = 3 To 70. mit welcher du die Zellen von Zeile 3 bis 70 füllen wolltest.
Der Vorteil der Formel ist, dass man die Formel in alle Zellen gleichzeitig in einem Schritt schreiben kann und nicht jede Zelle einzeln befüllen muss,
klar hätte man dafür auch schreiben können:

With Range(Sheets("Kalender").Cells(3, sp), Sheets("Kalender").Cells(70, sp))
aber das gleiche ergibt sich auch mit 

With Sheets("Kaldender").Cells(3, sp).Resize(68,1)
was ich persönlich sympathischer finde, weil man das Sheets() nur einmal benötigt.
von Zeile 3 bis Zeile 70 sind es halt 68 Zeilen und das Resize erweitert den Zellbereich auf die angegebene Anzahl Zeilen und Spalten.
mit dem 70-3+1 wollte ich dir nur zeigen, wie sich die Zeilenanzahl aus der gegebenen Start- und Endzeile berechnet.
Gruß Daniel
Anzeige
AW: SVERWEIS
18.09.2022 12:44:37
Dieter
Hey Daniel,
Sorry das ich mich jetzt erst melde, hatte zu tun.
Danke für Deine Erklärung, denke blicke jetzt bisschen durch.
Ein ganz kleines Anliegen hätte ich noch zu dem gut funktionierenden Code,
wenn er den Sverweis ausgeführt hat, das er mir die Schrift in eine Farbe setzt,
oder mir die Zelle einfärbt.
Wenn es nicht zu viel verlangt ist, einmal mit Schrift, und einmal mit Zelle
damit ich mir dann aussuchen könnte was mir besser gefällt.
Vielen Dank im Voraus dafür.
Gruß
Dieter

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige