Office: SVerweis in VBA

Hallo allerseits,

nach dem Durchforsten diverser Foren und meines getreuen VBA-Handbuches habe ich folgende Prozedur zu einer Userform-Combobox erstellt:

Code:

Ziel ist es, den Wert aus meiner Combobox "cmbZustMA" als Suchkriterium für einen Sverweis zu nutzen, der in der Matrix B16:J17 auf dem Arbeitsblatt "Dummies" einen Wert sucht und diesen auf dem Arbeitsblatt "Ausdruck" in Zelle F7 ausgibt.

Als Fehler erhalte ich u.A. : "Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.

Ich freue mich auf eure Anregungen.

Ps.: Wo kann ich denn im Bereich der WorksheetFunction.VLookup einen Unterstrich setzten, um das Ganze zu trennen. Meine Versuche will VBA nicht..

:)

 

Hallo Don,

versuche es mal so
wenn "ausdruck" und "Dummies" dein Tabellenblatt Name ist sollte es so gehen

Code:

« Gruß Peter9 »

 

Hi

Sverweis ist in VBA nicht nötig..., da Find das viel besser kann und deren Fehlerbehandlung einfacher ist.

Code:

*wink.gif*, ich will mal hoffen keines von B.H

 

Hallo Jungs,

vielen Dank für die Rückmeldungen.

@Peter:
Meine Range-Ausdrücke scheinen sauber (und kürzer), da gibt mir das Makro aus was ich will.
Wenn ich deinen Vorschlag nehme (und noch ein paar Anführungszeichen hinzufüge *wink.gif* ) erhalte ich leider wieder meinen bekannten Fehler "Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden."

@chris:
Danke für den Vorschlag. Der gibt in der Tat das gewünschte Ergebnis. Ich werde mich wohl mal intensiver mit With und Find auseinandersetzen müssen...
- Mein Handbuch ist übrigens von T.T. *wink.gif*

Jetzt weiß ich allerdings immer noch nicht, wie ich den SVerweis sauber benutzen kann. Einfach, um das Problem zu verstehen, statt es zu umgehen.

 

Der Fehler scheint beim Suchkriterium zu liegen.
Wenn ich dort direkt einen Suchbegriff eingebe, dann spurt mein Makro.

Hab das mal mit einer Variablen vom Typ String als Brücke versucht, da kommt aber wieder meine bekannte Fehlermeldung.

Habt ihr noch Ideen?

 

Hallo Name?,

mach doch mal eine kleine Demodatei fertig (ohne sensible Daten), anhand der das Problem erkennbar ist, dann ist es vielleicht besser nachvollziehbar.
Sverweis (auch als Arbeitsblattfunktion) ist sehr pedantisch, was die Vorgabe der Suchbegriffe und zu untersuchenden Matrix betrifft - die Zahl 123 ist eben nicht gleich dem Text "123" - hier unterscheidet die Funktion sehr genau.
Den Fall, dass der Sverweis wirklich mal etwas nicht findet, weil es nicht da ist, musst Du natürlich auch berücksichtigen.

Dir ist aber auch klar, wie der Sverweis funktioniert?

Code:

Gesucht wird im Bereich B16:B17 und zurückgegeben wird aus dem Bereich C16:C17.

Grüße
EarlFred

 

Hey EarlFred,

mit dem SVerweis in habe ich in Excel - durchaus erfolgreich - schon häufig gearbeitet. Die Funktionsweise scheint mir klar.

Mein Code funktioniert, wenn ich den Suchbegriff manuell in die Funktion eingebe. Daher denke ich ja auch, dass der Suchbegriff das Problem ist. Allerdings wundere ich mich, dass die Spaltenkennziffern (b;j) in meiner Suchmatrix klein sind.

Da ich allerdings will, dass die Combobox ausgewertet wird, hakt das Ganze.
Die Combobox zieht sich ihre Werte übrigens aus dem gleichen Bereich, den der VLookUp auswerten soll.

Aber wenn du etwas Zeit und Lust hast, kannst du das auch in der arg getrimmten angehängten Datei nachvollziehen *wink.gif*

Grüße
Pedro

 

Hallo Pedro,

am Beispiel wird's klar - hier liegt der Hund begraben:

Code:

Mit der zweiten Zeile löst Du also das erste Mal das Change-Ereignis der Combobox aus, wobei der Wert der Combobox dann "" ist. Zu einer leeren Zeichenfolge kann Sverweis aber keine Übereinstimmung finden - daher der Fehler (den man wie gesagt ohnehin abfangen sollte). Den ListIndex könnte man auch bei -1 belassen.

Code:

Grüße
EarlFred

 

Hallo Pedro,

hier noch eine Variante nimm den Listindex raus

dann So
Code:

oder wenns mit ListIndex sein soll

dann so
Code:

 

Jungs,

vielen Dank, ihr seid klasse.

In den .AddItem-Zeilen hab ich den Fehler gar nicht vermutet.
Und, da ich bislang dachte, dass mir der SVerweis im Zweifel wieder das #NV ausgibt, hätte ich dort oben so bald auch nicht gesucht.

Mir war auch nicht bewusst, dass ich den .ListIndex einfach weglassen kann. Aber damit ist dann auch das Item "" obsolet - sehr schön ^^.

Der aktuelle Code lautet also:
Code:

@Peter:
Der Vorschlag mit der Einlese-Schleife kommt mir sehr gelegen, das Thema Dynamisierung wäre jetzt als Nächstes aufgekommen ^^

TOP, nochmals vielen Dank euch Beiden.
Und damit verabschiede ich mich glücklich ins Wochenende.

Liebe Grüße
Pedro

 

Hallo Pedro,

zusammenhängende Tabellenbereiche aber bitte nicht mit Schleife einlesen, sondern "in einem Rutsch" über ein Array - das dann auch gleich der Combobox als Datengrundlage zugewiesen wird (Stichwort: List). Das ist allemal schneller im Ablauf und in meinen Augen auch einfacher programmiert.

Grüße
EarlFred

 

Vielen lieben Dank an alle für die ausführlichen Beschreibungen hier. Das funktioniert perfekt!