Sverweis mit VBA mit If Block
Daniel Frei
In meinem Posting vom 30.3.10 hatte ich das Problem, dass ich einen Sverweis
in VBA brauchte und nicht richtig umsetzte. Doch Peter Schleif half mir mit
folgendem Code:
[I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
Wenn jetzt aber der Mitarbeiter im Zielblatt nicht enthalten ist, erhalte
ich den Laufzeitfehler 1004. Ich möchte nun eine MsgBox erscheinen lassen,
wenn ein Mitarbeiter nicht enthalten ist. Da mein VBA für dies nicht reicht
suche ich bei euch um Hilfe. Zur genaueren Angabe gebe ich euch noch den
gesamte Code unten an.
Danke schon mal für eure Mühen und Hilfe
--
*********************************
Nur wer fragt kommt im Leben weiter.
Ein kluger Mann macht nicht alle Fehler selbst. Er gibt auch anderen eine
Chance. --
"Winston Churchill"
_______________________
Win 7
Office 2007 Enterprise
Code
Dim Stunden, Ferien As Variant ' Variablen der Eingabefelder
Dim Monat As Byte
Dim ZielBlatt As Variant ' Das Zielblatt ist ein Monatssheet
Sub Ausgabe2()
Sheets("Erfassen").Activate
MA = Sheets("Erfassen").Range("AD2").Value
Monat = Cells(16, 27).Value
Dim Eingabewert As Byte
Eingabewert = MsgBox("Wollen Sie den Monat" & " " &
Sheets("Erfassen").Range("AB2") & " " & vbNewLine & _
"und den Mitarbeiter" & " " & Sheets("Erfassen").Range("AD2") & " " & "?",
vbYesNoCancel)
If Eingabewert = vbYes Then
Cells(8, 8) = MA 'hier wird der Mitarbeiter geschrieben nach diesem soll
dann im Zielblatt gesucht werden
ElseIf Eingabewert = vbNo Then
MsgBox "Dann wählen Sie bitte einen anderen Monat oder Mitarbeiter!"
End If
ZielBlatt = MonthName(Monat)
'Sverweis auf Zelle I8 =SVERWEIS(H8;Januar!A2:E43;2;1)
[I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
'hier soll nun abgefangen werden wenn der MA nicht im Zielblatt vorhanden
ist.
End Sub
Peter Schleif
>
> In meinem Posting vom 30.3.10 hatte ich das Problem, dass ich einen Sverweis
> in VBA brauchte und nicht richtig umsetzte. Doch Peter Schleif half mir mit
> folgendem Code:
> [I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
>
> Wenn jetzt aber der Mitarbeiter im Zielblatt nicht enthalten ist, erhalte
> ich den Laufzeitfehler 1004. Ich möchte nun eine MsgBox erscheinen lassen,
> wenn ein Mitarbeiter nicht enthalten ist.
Eigentlich gibt es für solche Fälle Funktionen wie IsError(), die aber
bei VLookup/Match/etc. nicht greifen. Eine einfache Lösung wäre es, hier
ausnahmsweise mit OnError zu arbeiten. Nicht besonders schön, aber effektiv:
On Error GoTo nicht_gefunden
[I8]=WorksheetFunction.VLookup([H8],Sheets(ZielBlatt).[A2:B42],2,True)
nicht_gefunden:
If Err Then MsgBox "Nicht gefunden"
Err.Clear
Peter
Bernd P
Hallo Peter,
Du siehst doch dass der Code offenbar in einem Unternehmen eingesetzt
wird.
Hältst Du den oben genannten Code (den gesamten Ansatz) wirklich für
gut genug?
Viele Grüße,
Bernd
Andreas Killer
> [I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
...
> Wenn jetzt aber der Mitarbeiter im Zielblatt nicht enthalten ist, erhalte
> ich den Laufzeitfehler 1004. Ich möchte nun eine MsgBox erscheinen lassen,
Der Fehler 1004 ist ein "universeller Fehler" der aus div. Gründen
erscheinen kann, d.h. Du kannst aus ihm hier nicht automatisch folgern
das der Mitarbeiter nicht vorhanden ist. Der Fehler kommt immer wenn
irgendwas schief geht.
Mit dem WorksheetFunction-Objekt zu arbeiten ist zwar sehr bequem,
aber das Fehlermanagment dabei ist sch....wierig.
Warum suchst Du nicht mit Find nach dem Mitarbeiter? So könntest Du
Schritt für Schritt vorgehen und dabei auftretende Fehler auch
folgerichtig eingrenzen.
Exemplarisches Beispiel:
if IsEmpty(Range("H8")) then
MsgBox "Kein Mitarbeiter angegeben"
exit sub
endif
if not SheetExists(Zielblatt) then
MsgBox Zielblatt & " fehlt!"
exit sub
endif
set C = Sheets(ZielBlatt).Range("A2:B42").Find(Range("H8"), LookIn:=
xlValues, LookAt:= xlWhole)
if C is Nothing then
MsgBox "Mitarbeiter nicht gefunden"
exit sub
endif
Andreas.
Peter Schleif
> On 15 Apr., 22:10, "Daniel Frei" <u...@pcfrei.ch> wrote:
>
>> [I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
>
Liefert wegen True nicht das gleiche Ergebnis wie SVERWEIS.
Ob die Mitarbeiterliste tatsächlich sortiert vorliegt und ob es
überhaupt Sinn macht, den alphabetisch nächstkleineren Mitarbeiter
zurückzugeben, kann uns nur der OP verraten.
Peter
Thomas Ramel
Daniel Frei schrieb am 15.04.2010
> In meinem Posting vom 30.3.10 hatte ich das Problem, dass ich einen Sverweis
> in VBA brauchte und nicht richtig umsetzte. Doch Peter Schleif half mir mit
> folgendem Code:
> [I8] = WorksheetFunction.VLookup([H8], Sheets(ZielBlatt).[A2:B42], 2, True)
>
> Wenn jetzt aber der Mitarbeiter im Zielblatt nicht enthalten ist, erhalte
> ich den Laufzeitfehler 1004. Ich möchte nun eine MsgBox erscheinen lassen,
> wenn ein Mitarbeiter nicht enthalten ist. Da mein VBA für dies nicht reicht
> suche ich bei euch um Hilfe. Zur genaueren Angabe gebe ich euch noch den
> gesamte Code unten an.
Du könntest denselben Ansatz verwenden, der auch im Tabellenblatt klappt:
Prüfe mit ZÄHLENWENN() WorksheetFunction.Countif() ober der Name in der
ersten Spalte der Suchmatrix enthalten ist und hole die Infos über
SVERWEIS() erst dann wenn dies der Fall ist.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Vista Ultimate SP-1 / xl2007 SP-1]
Peter Schleif
>
> Du siehst doch dass der Code offenbar in einem Unternehmen eingesetzt
> wird.
> Hältst Du den oben genannten Code (den gesamten Ansatz) wirklich für
> gut genug?
Nein, nicht _wirklich_
Aber das gilt IMHO für 80-90% des VBA-Codes den Fragende hier posten.
Streng genommen, müsste man in jedem dieser Fälle dem OP nahe legen,
seinen Ansatz - möglicherweise sogar sein ganzen Konzept - zu überdenken
und mehrseitige Verbesserungs-Vorschläge geben.
Ich habe jedoch beruflich, privat und hier im Usenet die Erfahrung
gemacht, dass solche "missionierenden Antworten" nicht nur ungern
gesehen sind; sie werden auch schlichtweg nicht angenommen.
Die Fragenden haben oft tagelang oder gar wochenlang an dem Code
gearbeitet und sind darum gar nicht bereit, alles in die Tonne zu
kloppen. Darum beschränke ich mich bei konkreten Fragen meist auf
konkrete Antworten.
Im vorliegenden Fall könnte man sicherlich eine etwas sauberere Lösung
verwenden. Siehe unten. Warten wir mal ab, was der OP dazu sagt und ob
er den vierten Parameter wirklich auf "True" haben möchte. Falls nicht,
greifen ja die Lösungen von Andreas und Thomas – oder man verwendet in
folgendem Code-Fragment = statt >=
Peter
Dim z As Long
[I8] = ""
With Sheets(ZielBlatt).[A2:B42]
For z = .Rows.Count To 1 Step -1
If .Cells(z, 1) <> "" And [H8] >= .Cells(z, 1) Then
[I8] = .Cells(z, 2)
Exit For
End If
Next
End With
If [I8] = "" Then
MsgBox "Nicht gefunden"
End If
Daniel Frei
Vielen Dank für eure Hilfe und die vielen Vorschläge
Ich gebe mich mit der Lösung von Peter zufrieden. Natürlich steckt hinter
der Datei, wie Peter schon bemerkt hat, die eine bzw. andere Stunde Arbeit.
Nicht, dass ich das ganze nicht in die Tonne werfen würde, sondern eher weil
mein VBA nicht so weit ist wie eures. Ich denke deshalb ist auch den meisten
OP's schon sehr geholfen, wenn Ihr die Codes so abändert, dass wir (Laien)
auch die Möglichkeit haben, ein wenig mit- bzw. weiterzukommen.
In diesem Sinne danke ich euch für eure Hilfe und bin der Meinung, das es
für die einen Posts hier ein gutes Sprichwort gibt:
Da staunt der Laie und der Fachmann wundert sich
Aber wichtig ist doch meist, das es nachher genau so geht wie es sich der OP
erhofft oder erwünscht hat.
Grüsse aus der Schweiz
Daniel