Funktion zur suche eines Wertes in einer Tabelle

  • Excel

Es gibt 9 Antworten in diesem Thema. Der letzte Beitrag () ist von Till.

    Funktion zur suche eines Wertes in einer Tabelle

    Hallo zusammen,

    ich möchte gern in einem Excel-VBA Projekt und Funktion anwenden welche mir aus einer Wertetabelle einen Bestimmten Wert heraussucht.
    Ich suche hierzu nach meinem Eingabewert in Spalte A und möchte dessen Partner aus Spalte B als Ergebnis erhalten.

    Beispiel:
    .......A...........B...............Die Werte in A und B sind nicht per Formel berechnet. Es sind frei gewählte Werte.
    1 |...x...........y................ Und auch in meinem Programm sind die Werte nicht berechenbar.
    2 |...1...........3
    3 |...1,1.........5
    4 |...1,2.........32
    5 |...1,3.........187
    6 |...1,4.........18
    7 |...1,5.........25
    8 |...1,6.........36
    9 |...1,7.........45

    Wenn ich nun 1,3 in meine Funktion eingeben würde sollte sie mir 187 als Ergebnis zurückgeben.
    Ich verwende hierbei gezielt eine Funktion weil die Wertetabelle an verschiedenen stellen im Programm benötigt wird. Dafür habe ich eine Public Function in den Codebereich von Tabelle1 eingefügt (da in Tabelle1 meine Wertetabelle ist)

    Bisher bin ich wie folgt Vorgegangen um die korrekte Zelle in spalte A zu finden:

    Visual Basic-Quellcode

    1. Public Function Finder(q)
    2. With Worksheets("Tabelle1").Range("A:A") ' "A:A" da die Wertetabelle vom Anwender beliebig erweiterbar ist
    3. Set Ergebnis = .Find(q)
    4. End With
    5. Finder = Ergebnis
    6. End Function
    8. Public Sub test()
    9. q = Finder(q)
    10. MsgBox q
    11. End Sub
    12. ​


    Ich sehe leider meinen Fehler nicht, warum Es mir jetzt Permanent Fehlermeldungen bringt das eine "Objektvariable oder With-Blockvariable nicht festgelegt" ist.
    Es hat allerdings auch schonmal was gemacht, dabei aber egal welche Eingabe ich tätige Zelle A2 als Ergebnis angezeigt hat, bzw die MSGBOX den Wert von Zelle A2 ausgibt.


    Des weiteren komme ich mit dem Zelle "eins nach recht rücken" nicht klar.
    ein einfaches

    Visual Basic-Quellcode

    1. ​Ergebnis = Ergebnis.column + 1
    hat mich leider nicht ans Ziel geführt. :D
    Und da es keine ActiveCell ist sollte doch auch

    Visual Basic-Quellcode

    1. ActiveCell.Offset(0, 1).select
    nichts bringen, oder irre ich mich?

    Gruß Till

    Deine Funktion ist schlampig programmiert, verwende Option Explicit!
    Beispielsweise hat deine Funktion und deren Parameter keine Typenangabe.

    Versuch's mal mit

    Visual Basic-Quellcode

    1. Public Function Finder(q As String) As String
    2. Dim Ergebnis As Range
    3. Set Ergebnis = Worksheets("Tabelle1").Range("A:A").Find(q, LookIn:=xlValues, LookAt:=xlWhole)
    4. If Not Ergebnis Is Nothing Then Finder = Ergebnis.Offset(0,1).Value
    5. End Function


    Aber wenn ich mir's recht überlege, wäre da die Verwendung einer SVERWEIS-Formel wesentlich effizienter als eine UDF.
    Die ist bestimmt um Faktor 100 schneller.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    Ich möchte mich für meine Nachlässigkeit hier entschuldigen!
    Ich verwende in meinem Projekt natürlich Option Explicit und dimensioniere alle Variablen!

    Nun zu deinem Vorschlag
    -Wenn ich deinen Vorschlag einfach so einsetze gibt mir die Funktion als Ergebnis die erste leere Zelle nach der Wertetabelle!

    -Warum verwendest du
    Public Function Finder(q As String) As String

    ?
    Ich verstehe den Grund nicht! Rückgabewert soll ja ein Single-Wert sein mit dem ich weiter rechnen kann!
    -> einfach alles auf Single ändern funktioniert aber auch nicht. Dann meldet er "Objektvariable oder With-Blockvariable nicht festgelegt"

    Wenn das ganze numerisch sein soll

    Visual Basic-Quellcode

    1. ​Public Function Finder(q As Double) As Double
    2. Dim Ergebnis As Range
    3. Set Ergebnis = Worksheets("Tabelle1").Range("A:A").Find(q, LookIn:=xlValues, LookAt:=xlWhole)
    4. If Not Ergebnis Is Nothing Then Finder = Val(Ergebnis.Offset(0,1).Value)
    5. End Function

    Hat allerdings ggf. das Problem, dass wenn in der Zelle keine Zahlen stehen, dass ein Fehler zurückgegeben wird.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Guten Morgen,

    auch dieser Versuch bleibt leider Erfolglos. Nun ist egal bei welcher Eingabe das Ergebnis = 0.
    Ich habe auch mal versucht am Ende der Function mir die Adresse von Ergebnis ausgeben zu lassen. Dann kommt allerdings sofort wieder "Objektvariable oder With-Blockvariable nicht festgelegt"!?

    Dann musst du halt einen Breakpoint im Code setzen und debuggen.
    Ich kann nicht richtig erahnen, wie dein Dokument aussieht.
    Dein Beispiel in Post #1 ist zu schwammig als dass man die Excel-Struktur erkennen kann.

    Nochmals: Hast du mal über die Verwendung von SVERWEIS nachgedacht?
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    Habe jetzt probiert mit SVERWEIS (bzw. VLookUp) mein Ziel zu erreichen und bin auch hiermit gescheitert.

    Visual Basic-Quellcode

    1. Dim q as Single
    2. Dim Ergebnis as Range
    3. Dim Datenbasis as Worksheet
    4. Dim letzteZeile as Long
    6. Public Function Finder(q)
    8. Set Datenbasis = ThisWorkbook.Worksheets("Tabelle1")
    9. letzteZeile = Datenbasis.Range("A" & Rows.Count).End(xlUp).Row
    10. 'Den Wert der letzten Zeile bestimme ich im Programm, da ich dem Anwender die Möglichkeit der Erweiterung der Tabelle offen lasse!
    11. Ergebnis = WorksheetFunction.VLookup(q, "A2:B" & letzteZeile, 1, False)
    12. Finder = Ergebnis
    14. End Function
    15. Public Sub test()
    17. InputBox (q)
    18. q = QuantilFinder(q)
    19. MsgBox q
    21. End Sub


    Mal abgesehen davon das ich hier sowieso nur nach q in der Spalte A suche (zumindest habe ich die Funktion so verstanden! / korrigiere mich wenn ich Falsch liege!) und mir noch nicht den gesuchten Wert aus Spalte B zurückgeben lasse kommt bereits hier "Anwendungs- oder objektdefinierter Fehler"?

    Ich möchte nochmal kurz zusammenfassen, was ich mit dieser Funktion erreichen will.

    Die Benutzeroberfläche des Projektes besteht aus UserForm's. Hier trägt der Anwender mehrere Werte in TextBox's ein, mit welchen das Programm rechnen soll.
    In einer der TextBox's steht der Wert q.Für diesen Wert q habe ich auf "Tabelle1" eine Wertetabelle angelegt. Die Eingabe für den Wert q habe ich mit TextBox_Keypress, TextBox_AfterUpdate und durch auf-/abrunden soweit begrenzt, das q nur so gewählt werden kann, das er definitiv auch in meiner Spalte A Vorkommt (für den Fall, dass ein Anwender die Tabelle erweitert, kann ich selber eine Überprüfung einbauen!). In dieser Wertetabelle ist jedem Wert q (SpalteA) ein Wert (nennen wir ihn w) in Spalte B zugeordnet. Die Funktion soll nun den Wert q in Spalte A finden und mir den Wert w aus Spalte B zurückliefern.
    BSP: Wenn die Funktion nun also den Wert q in Zelle "A59" Findet, soll sie als Ergebnis den Wert (also den .Value) von "B59" zurückliefern.

    Ich hoffe diese Beschreibung ist jetzt eindeutig und hilft dir dabei mir zu helfen :D
    Grüße
    Till

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Till“ ()

    Was ist QuantilFinder??


    Ich könnte mir vorstellen, dass die Datentypen nicht stimmen.
    Achte darauf, dass du Zahlen als Text und numerische Zahlen nicht durcheinander würfelst.
    Wenn du in einer TextBox eine Eins eingibst, steht da "1" und nicht 1 drin!
    Das ist was völlig anders. Ggf. wird das irgendwo automatisch konvertiert, aber vielleicht auch nicht.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    QuantilFinder ist nicht das Problem! Habe ich nur vergessen zu ändern als ich den Code hier eingefügt habe!

    Es Funktioniert nun!

    Visual Basic-Quellcode

    1. Public Function QuantilFinder(q) As Double
    3. Set Datenbasis = ThisWorkbook.Worksheets("Tabelle1"),
    4. letzteZeile = Datenbasis.Range("A" & Rows.Count).End(xlUp).Row
    5. With Datenbasis
    6. Set Ergebnis = Datenbasis.Range("A2:A" & letzteZeile).Find(q, LookIn:=xlValues, LookAt:=xlWhole)
    7. If Not Ergebnis Is Nothing Then QuantilFinder = Ergebnis.Offset(0, 1).Value
    8. End With
    10. End Function
    12. Public Sub test()
    14. q = InputBox("Eingabe")
    15. q = QuantilFinder(q)
    16. MsgBox q
    18. End Sub


    Ich habe deine Idee die gesamte Zeit in der Form

    Visual Basic-Quellcode

    1. InputBox (q)

    getestet. Bis mir soeben auffiel, dass das ja kompletter Käse ist. Ich danke dir für deine Lösung zu meinem Problem!
    Gruß
    Till

    Gleiches Thema anderes Problem ;( .

    Habe jetzt noch in dieser Suchfunktion den Fall abgedeckt, das wenn eine Kommazahl abgefragt wird, diese aber nicht so in der Tabelle vorkommt, auf ihre Vorletzte Kommastelle gerundet wird (also 0,987654 rund 0,98765). Dieser gerundete Wert wird der Funktion nun wieder zugeführt. Der Spaß verläuft also rekursiv. Wenn ich nun meine Funktion mit einem Wert füttere, welcher so direkt in der Tabelle vorkommt, gibt Sie mir das Korrekte Ergebnis. Muss die Funktion aber erst einmal in die Rekursion gehen ist der Rückgabewert immer 0!

    Visual Basic-Quellcode

    1. ​
    2. Option Explicit
    3. Dim Ergebnis As Range
    4. Dim letzteZeile As Long
    5. Dim Datenbasis As Worksheet
    6. Dim DezSep As String
    7. Dim a As Integer
    8. Dim b As Double
    10. Public Function QuantilFinder(q As Double) As Double
    11. Debug.Print "Quantilsuche für " & q
    12. Set Datenbasis = ThisWorkbook.Worksheets("Tabelle1")
    13. letzteZeile = Datenbasis.Range("A" & Rows.Count).End(xlUp).Row
    14. With Datenbasis
    15. Set Ergebnis = Datenbasis.Range("A2:A" & letzteZeile).Find(q, LookIn:=xlValues, LookAt:=xlWhole)
    16. If Ergebnis Is Nothing Then
    17. DezSep = IIf("0.5" * 2 = 1, ".", ",")
    18. a = Len(Str(Abs(q))) - InStrRev(CStr(Abs(q)), DezSep) - 1
    19. q = WorksheetFunction.Round(q, a)
    20. QuantilFinder (q)
    21. Else:
    22. Debug.Print "Quantil = " & Ergebnis.Offset(0, 1).Value 'Ergebnis.Offset hat hier den Korrekten wert
    23. QuantilFinder = Ergebnis.Offset(0, 1).Value 'liefert mir aber immer, wenn es mindestens einen rekursiven durchlauf hatte eine 0
    24. End If
    25. End With
    26. End Function
    28. Sub test()
    29. b = InputBox("b eingeben")
    30. b = QuantilFinder(b)
    31. Debug.Print "Quantil = " & b
    32. End Sub


    qw
    0,992,3263
    0,982,0573
    0,971,8808
    0,961,7507
    0,951,6449
    0,941,5548
    0,931,4758
    0,921,4051
    0,911,3408
    0,901,2816

    Kleiner Beispielausschnitt aus der Tabelle!

    Ich trete nun schon einige Stunden auf der Stelle, habe einige Forenbeiträge und bin langsam am Verzweifeln. Ich Verstehe einfach nicht warum die Funktion einen Wert zurück gibt wenn keine Rekursion notwendig war, ansonsten jedoch nicht.

Facebook
Telefonspende