Office: SVerweis über VBA

Hallo VBA´ler

ich möchte einen SVerweis über VBA abbilden.
Meine Formel sieht so aus:
Code:

Das Suchkriterium steht in Tabelle1, Spalte A, das Ergebnis soll in Tabelle1, Spalte B.
Kann mir hier jemand helfen? *Smilie
Wenn ja, BITTE mit Kommentierung, damit ich es auch verstehe! *wink.gif*

Schon mal VIELEN DANK

Gruß Mücke

:)

 

Hi Mücke,

der Code lautet

Code:

Habe das Ergebnis im Beispiel einfach mal in Zelle B1 des aktiven Blattes geschrieben.

Musst halt drauf achten, welches Blatt bei Ausführung des Makros grad aktiv ist. Ggf. analog z. B. Sheets("Tabelle1").[A1] als ersten Parameter einsetzen.

Da der Aufbau analog zur Funktion ist, erübrigt sich m. E. eine größere Erläuterung.

Grüße
EarlFred

 

Moin, mücke,

der wahrlich einfachste Weg, ane eine VBA-Variante der Formel zu kommen, ist das Starten des Makro-Rekorders, dass Editieren der Formelzelle und Beenden sowie danach das Beenden des Rekorders. Dann kannst Duz dir in der VBE den Code selbst betrachten.

Code:

Und was meinst Du, was bei Verwendung der Formel in VBA anders ist als bei einer Tabellenfunktion? *wink.gif*

 

Hi EarlFred,
hi jinx,

Danke für die Code´s und die Erläuterungen. Werde diese gleich mal testen!

... schon passiert, geht gut! *Smilie

@jinx

Die Formel kann ich in die Zellen kopieren, da wo ich Sie brauche,
bei VBA habe ich so meine Schwierigkeiten, so wie hier!
Im Beispiel steht A1, was ist mit den anderen Zellen A2, A3 usw. Wie bekomme ich die Formel dort hin?

Gruß Mücke

 

Hallo,

ich noch mal.

Habe den Code etwas verändert. Wollte alle Zellen in den SVerweis integrieren!
Nun bekomme ich zwar ein Ergebnis, doch leider steht dazwischen immer ein #NV ! Was mach ich falsch?
Code:

Kann mir hier noch mal jemand helfen?! *Smilie

Vielen Dank

Gruß Mücke

 

#NV bedeutet, daß zu dem angegebenen Suchkriterium kein Wert gefunden wird.

Das liegtdaran, daß das letzte Argument im SVERWEIS "False" ist, und demnach nur nach exakten übereinstimmnungen gesucht wird.

mfg Sturmovik

 

Hi Sturmovik,

OK, Danke! *Smilie
Aber wie bekomme ich diese Meldungen weg? Ich brauche ja nur die Übereinstimmungen, ohne #NV

Gruß Mücke

 

Kommt drauf an was du haben willst.

Wenn die nächstbessere Übereinstimmung gesucht wird läßt du das letzte Argument einfach weg. Dazu sollte aber der Suchbereich aufsteigend sortiert sein.

Wenn das Ergebnis einfach leer sein soll mußt du das #NV irgendwie behandeln, das hab ich aber mit VBA noch nicht gemacht. Hab auch grade kein Excel zu Hand um zu probieren.

Hoffe, das hilft dir weiter.

mfg Sturmovik

 

hiho

versuche mal folgendes
Code:

 

HAllo

Eine Formel kannst du auch direkt mit FomulaLocal eingeben.
Musst nur drauf achten das jedes " in der Formel noch ein " dazubekommt und die $-Zeichen passend gesetzt sind....

Option Explicit

Public Sub test()
With Sheets("Tabelle1").Range("C3:C33")
.FormulaLocal = _
"=WENN(ZÄHLENWENN(Feiertage!$A$1:$A$18;A1);SVERWEIS(A1;Feiertage!$A$1:$B$18;2;0); """")"
'.Value = .Value 'In Festwerte umwandeln
End With
End Sub


Zur Fehlerbehandlung #NV gibt es mehrere Möglichkeiten.

unter anderem:
=Wenn(Istfehler(Sverweis());"Irgendwas";Sverweis())
oder:
=wenn(Zählenwenn(ErsteSpalte von Matrix);Sverweis();"Irgendwas")

Das eine sowie das andere hat seine Besonderheiten die man in Sonderfällen beachten muss.

ransi

 

Hallo zusammen,

Vielen Dank für die Tipps *Smilie und deren Erläuterungen!

@chris-kaiser
Dieser Code funktioniert wunderbar *biggrin.gif* , so wollte ich es haben!

@ransi
bei dieser Variante habe ich eine falsche Zuordnungen der Daten bekommen, immer um 2 versetzt! Warum?
Die Formel wird auch mit in die Zellen geschrieben! Ist das richtig, soll/darf das so sein?

Gruß Mücke

 

Hallo,

ich habe ein ganz ähnliches Anliegen.

Ich versuche mit einem Makro in einer Tabelle eine Spalte einfügen zu lassen und bei dieser dann einen Sverweis auszuführen.

Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Table1[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Location"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[Personnel Subarea]],TN_location.Range(B:C),2,0)"
On Error GoTo 0

wenn ich das durchlaufen lasse, gibt mir Excel den Sverweis für die Range (B:P:P) zurück.

auch wenn ich das mit dem Makro recorder einmal aufnehme und dann als Range beispielsweise
"Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP([@[Personnel Subarea]],TN_location!C[-14]:C[-13],2,0)"

nehme, springt Excel bei der tatsächlichen Kalkulation auf die Range (B:Q:P).

nun bin ich etwas ratlos, was ich da noch machen könnte.

Habt ihr Ideen?

Vielen Dank.

 

Hallo zusammen,

ich bin noch absoluter VBA-Anfänger, versuche mich aber jetzt nach und nach daran, verschiedene Excel-Funktionen über ein Makro ausführen zu lassen.

Ich möchte gerne einen Sverweis in Spalte P des Arbeitsblattes "Übersicht" schreiben. Die Besonderheit hierbei ist, dass das Blatt "Übersicht" regelmäßig erweitert wird, sprich neue Zeilen hinzukommen. Außerdem soll der Sverweis automatisch in alle Zeilen der Spalte P geschrieben werden, deshalb die For-Schleife.

Hier mein Code:


Sub Check ()

Dim Zeile As Long
Dim ZeileMax As Long

With Worksheets("Übersicht")
ZeileMax = ActiveSheet.UsedRange.Rows.Count

For Zeile = 2 To ZeileMax
ActiveSheet.Cells(Zeile, 16) = WorksheetFunction.VLookup(ActiveSheet.Cells(Zeile, 2), Worksheets("OEMs").Range("A2:B7"), 2, False)
Next Zeile

End Sub


Für die ersten beiden Zeilen funktioniert es, danach kommt jedoch die Fehlermeldung "Die VLookup-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeorndet werden." Eigentlich sollte in der dritten Zeile ein #NV erscheinen - kann das damit zusammenhängen?


Lieben Gruß
Domenico