Excel VBA Suche in benannten Zellenbereichen und Ausgabe von Wert daneben

[excellover123]

Hallo,

ich habe folgende Situation:

Ich möchte in gewissen Zellenbereichen, die einen Namen haben (z.B. Bereich C8 bis D16 heißt Table1A, Bereich C20 bis D28 heißt Table 1B etc) einen gewissen Wert suchen und die Zelle daneben ausgeben als Ergebnis und das mit VBA. Ich arbeite im Excel/VBA gerne mit Zellennamen und Bereichsnamen, weil ich diese Excel Anwendungen meistens lange verwende und in verschiedene Kombinationen zusammenbaue, damit das universal und übersichtlich bleibt, vergebe ich gerne Namen.

Ich habe schon im Internet geschaut und ich glaube ich bin sehr nahe dran. Im Prinzip habe ich so eine ähnliche Anwendung wie hier, nur etwas komplexer:

https://www.denisreis.com/excel-vba-eige...le-finden/

Ich habe mal schnell ein Muster zusammengebaut, dieses ist im Anhang. Der Code soll am Ende dann so einfach wie möglich sein, weil ich bei meiner echten Excel noch mehr Bedingungen und Abfragen hab, das Beispiel als Anhang zeigt aber die grundsätzliche Anwendung.

Ihr würdet mir echt damit helfen bitte.

Danke schon mal für Eure Zeit.

[MisterBurns]

Hallo,

diese Vorgehensweise halte ich für ganz schlecht und du hast in deinem Code bereits selbst erkannt, warum das so ist. Je mehr Tabellen es gibt, umso komplexer wird das Ganze. Bei jeder zusätzlichen Tabelle muss der Code angegriffen und überarbeitet werden. Das bedeutet, jedes mal neu einlesen und eindenken. Falls es mal jemand anderer als du selbst machen muss, wird der/diejenige dich verfluchen.

Weiters hast du ein paar grundlegende Fehler im Code:
1) Du fragst zwar ab, ob "mit" oder "ohne" gefüllt sind, nicht aber, ob "A" oder "f" gefüllt sind.
2) Eine Abfrage, ob beides gefüllt ist, erfolgt nicht. Dies ist aber auch schlecht, da dies zu falschen Resultaten führen kann.
3) Die Zeile

Code:

If ohne = "x" And A > "5" Then

ist falsch, du machst aus der Zahl 5 einen Text und Text lässt sich nicht auf größer/kleiner vergleichen.
4) Die Abfragen lauten zwar auf < bzw. > 5, die 5 selber wird aber ausgespart.
5) Du wiederholst die gleiche Abfrage mehrmals

Code:

    If ohne = "x" And A > "5" Then

steht bereits im ersten If-Block und dann wieder im vierten. Falls die Bedingung zutrifft, wird das Ergebnis aus Block 1 dann vom Ergebnis aus Block 4 einfach überschrieben.

So viele If-Blöcke sind ziemlich nutzlos, bzw. können sogar das Ergebnis verfälschen. Dafür gäbe es zB ElseIf

Code:

If ohne ="x" And A < 5 Then
    'Ergebnis 1
ElseIf ohne = "x" And A >= 5 Then
    'Ergebnis 2
End If

Alternativ eine einfache Verschachtelung

Code:

If ohne = "x" Then
    If A < 5 Then
        'Ergebnis 1
    Else
        'Ergebnis 2
    End If
End If

Das schafft zumindest ein kleines bisschen mehr Übersichtlichkeit. Allerdings sollten das meines Erachtens höchstens 3 solcher Blöcke sein, sonst kann das ja kein Mensch mehr einfach nachvollziehen.

Die richtige Vorgehensweise wäre eine einzige Datentabelle nach dem Schema wie auf dem Bild. Das lässt sich auch wunderbar mittels Formel auswerten, VBA ist dazu unnötig. Suche im Netz dazu nach SVERWEIS mit 2 Bedingungen, da findest du Treffer zuhauf.

[excellover123]

Hallo,
....

Danke ich hab das meiste davon eingearbeitet.

Das mit dem SVERWEIS mit mehreren Bedingungen hab ich schon versucht, meine echte Excel ist leider wesentlich komplexer als das Muster, was ich angehängt hab. Ich hab das bisher leider noch nicht geschafft, die Daten in eine einzelne Tabelle zu verarbeiten, weil ich leider viel mehr Bedingungen habe, darum wollte ich auf VBA wechseln.

Könnte sich bitte noch jemand dieses Code-Fragment ansehen?


If A > 5 Then
        Set Ergebnis = wks_TEST.Range("Table1A").Columns(1).Find(what:=wks_TEST.Range("f").Value)
        wks_TEST.Range("testwert").Value = wks_TEST.Cells(Ergebnis.Row, 2).Value
    ElseIf A < 5 Then
        Set Ergebnis = wks_TEST.Range("Table1C").Columns(1).Find(what:=wks_TEST.Range("f").Value)
        wks_TEST.Range("testwert").Value = wks_TEST.Cells(Ergebnis.Row, 2).Value
    End If

Was genau mache ich hier falsch? Bei mir kommt immer die Fehlermeldung in der fett markierten Zeile:
"Laufzeitfehler 1004. Die Methode Range für das Object _Worksheet ist fehlgeschlagen

Bitte es wäre mir wirklich eine große Hilfe.

[Klaus-Dieter]

Hallo,

da ich deine Datei nicht kenne, rate ich mal, meiner Ansicht nach ist das: Range("Table1A").Columns(1) falsch. Die Range müsste den Suchbereich definieren. Das kann ich hier nicht erkennen. Was soll Table1A sein?

[excellover123]

Hallo,

da ich deine Datei nicht kenne, rate ich mal, meiner Ansicht nach ist das: Range("Table1A").Columns(1) falsch. Die Range müsste den Suchbereich definieren. Das kann ich hier nicht erkennen. Was soll Table1A sein?

Hallo Klaus-Dieter,

im ersten Beitrag hängt die Excel Datei mit drinnen.
Table1A ist ein Zellenbereich, der von C8 bis D16 geht.

[Klaus-Dieter]

Hallo,

ich denke das geht so nicht, allerdings verstehe ich auch nicht, was das Ziel der Geschichte ist.

[excellover123]

Hallo ich hab noch ein bisschen herumprobiert, hier nochmal ein Screenshot und die letzte Version, ich bin anscheinend schon sehr knapp dran, es scheint mir aber dort und da noch an den Grundlagen VBA zu fehlen (ich habe es erst selbst nach vielen vielen Jahren gerade erst wieder gelernt).

Im Screenshot:

1) Ich würde gerne statt diesem C8:C16 einen Namen im Excel vergeben "Table1A", ich schaffe es aber nicht, den richtig anzusprechen?

2) Diese Zeile funktioniert ansonsten mittlerweile einwandfrei und findet den Wert 8

3) Hier hab ich das Problem, dass in dem .Cells (Ergebnis.Row,2) rauskommt, dass er Spalte 2 insgesamt vom Excel nimmt (das hab ich mit der 99 bestätigt). Er sollte aber in dem Bereich, wo er 8 findet, also im Moment C8:C16 oder wie ich mir wünschen wirde "Table1A" die Spalte daneben finden und dort den Wert ausgeben.

Kann mir bitte wer helfen?

[Klaus-Dieter]

Hallo,

versuche bitte zu beschreiben, wie aus den Vorgaben in Zeile 3 der Wert 99 entstehen kann. Und was bedeutet mit bzw. ohne?

[excellover123]

Hallo,

versuche bitte zu beschreiben, wie aus den Vorgaben in Zeile 3 der Wert 99 entstehen kann. Und was bedeutet mit bzw. ohne?

Hallo nochmal,

in meinem echten Excel hab ich viele Bedingungen abzudecken, die Kriterien bauen sich folgendermaßen auf:

Stufe 1: Es ist entweder "mit" oder "ohne".

Stufe 2: Unter "mit" zB die Bedingungen dass A größer oder kleiner 5 ist.

Stufe 2: Unter "ohne" gibt es dann auch die zb die Bedingungen dass f größer oder kleiner 5 ist.


Wie kann aus den Vorgaben in Zeile 3 der Wert 99 entstehen:

Das steckt im Prinzip hier drinnen:

'Überprüfung ob mit oder ohne ausgewählt wurde
    If ohne = "x" Then
        GoTo Ohne_L
    ElseIf mit = "x" Then
        GoTo Mit_L
    End If
   
    'hier sollen die unterschiedlichen Fälle abgefragt werden, dann die richtige Tabelle ausgewählt werden und dann der richtige Wert gefunden und die Zeile daneben ausgegeben werden
Ohne_L:
 
    If A > 5 Then
        Set Ergebnis = wks_TEST.Range("C8:C16").Find(what:=wks_TEST.Range("f").Value, lookat:=xlWhole)
        wks_TEST.Range("testwert").Value = wks_TEST.Cells(Ergebnis.Row, 2).Value
    ElseIf A < 5 Then
        Set Ergebnis = wks_TEST.Range("Table1C").Find(what:=wks_TEST.Range("f").Value, lookat:=xlWhole)
        wks_TEST.Range("testwert").Value = wks_TEST.Cells(Ergebnis.Row, 2).Value
    End If

In den Angaben in Zeile 3 ist ohne markiert, also hüpft er zur Sprungmarke Ohne_L:
Dort findet er dann im Bereich C8:C16 den Wert von f =8.
Bis dahin funktioniert noch alles richtig.
mit der Zeile wo Ergebnis.Row,2 steht, nimmt er dann den Wert aus der Spalte 2 in der Zeile, wo er 8 findet. Ich hab 99 dort mal zum testen hingeschrieben um zu schauen, ob das so funktioniert.
Ich möchte aber stattdessen in diesem Fall den Wert aus der Spalte finden, wo er 8 findet + eine Spalte daneben.

---

So hat es geklappt mit dem auslesen:

If A > 5 Then
        Set Ergebnis = wks_TEST.Range("C8:C16").Find(what:=wks_TEST.Range("f").Value, lookat:=xlWhole)
        wks_TEST.Range("testwert").Value = wks_TEST.Cells(Ergebnis.Row, Ergebnis.Column + 1).Value



Jetzt hab ich nur mehr ein Problem, wie kann ich das C8:C16 durch einen Bereichsnamen ersetzen? Geht das?

Lg

[excellover123]

Und es hat sich auch die Bereichsnamensgeschichte gelüftet, der Fehler war ganz einfach ein Tippfehler


Tut mir leid für die blöden Fragen, manchmal hunzt es einfach....