VBA für index-vergleich

[HH-Joh.]

Hallo in die Runde,
für den SVERWEIS gibt es in VBA VLookup.

Gibt es soetwas ähnliches auch für Index-Vergleich, der in einer Tabelle nach einem Kundennamen sucht und die Kundennummer links davon auswirft.
Siehe Anhang

Code: Alles auswählen

Sub Kunde()
Dim Suchkriterium As String
Dim Kdno As String

With ActiveSheet
    Suchkriterium = ActiveCell.Value
    MsgBox Suchkriterium
        If Suchkriterium <> "" Then
                    Kdno = WorksheetFunction.VLookup(Suchkriterium, Worksheets("Kunden").Range("A1:B7"), 2, False)
              MsgBox Kdno
         End If
   End With
End Sub


Danke für Antwort

[Hilfloser Bastler]

Hallo!

Die Excel-Formel IINDEX wird in VBA mit INDEX übersetzt

Grüße vom Bastler

Nachtrag: Die angehängte Liste DEU/ENG ist weder vollständig noch aktuell, hilft dennoch gelegentlich

[HKindler]

Hi,

man kann auch einfach die Formel in eine Zelle (z.B. A1) eingeben und sich diese dann im Direktfenster mit ?[A1].Formula (oder einer beliebigen anderen Form der Zellangabe wie Range oder Cells) anzeigen lassen. Damit hat man dann immer eine eine aktuelle Übersetzung.

[Hilfloser Bastler]

Helmut's Tipp ist unangefochten viel besser! DANKE SCHÖN!

[HH-Joh.]

Danke Euch beiden.

Das erste hat schon mal geklappt, aber meine Umsetzung noch nicht.

Code: Alles auswählen

?Worksheets("Kunden").Range("G10").Formula
=INDEX(Kunden!$A$2:$B$9,MATCH($G$3,Kunden!$B$2:$B$9,),1)

jetzt mache ich aber einen Fehler, weiß nicht wo.

Code: Alles auswählen

Sub Kunde()
Dim Suchkriterium As String
Dim Kdno As String
Dim tbl_kd As Range
Dim tbl_Name As Range
Dim Suchzelle As String


'With ActiveSheet
    'Suchkriterium = ActiveCell.Value
    'MsgBox Suchkriterium
   
    Suchzelle = Worksheets("Kunden").Range("G3")
    MsgBox Suchzelle
   
   tbl_kd = Worksheets("Kunden").Range("A2:B9")
    MsgBox tbl_kd
    'tbl_Name = Worksheets("Kunden").Range("B2:B9")
        If Suchkriterium <> "" Then
                    'Kdno = WorksheetFunction.VLookup(Suchkriterium, Worksheets("Kunden").Range("A1:B7"), 2, False)
                    'Kdno = INDEX(tblKd,MATCH(G3,tbl_Name,),1)
                   
                  Kdno = WorksheetFunction.Index(Worksheets("Kunden").Range("A2:B9"), Match(Worksheets("Kunden").Range("G3"), Worksheets("Kunden").Range("B2:B9"),), 1)
              MsgBox Kdno
        End If
   'End With
End Sub


Er springt zur vorletzten Klammer.

Weiß jemand Rat?

DAnke im voraus.

Gruß

Johanna

[HKindler]

Hi,

schreib einmal vor das "Match" noch ein "WorksheetFunction."

[HH-Joh.]

Danke Helmut.

das hat geklappt.

Code: Alles auswählen

Sub Kunde()

Dim Kdno As String
Dim Suchzelle As String

Suchzelle = Worksheets("Kunden").Range("G3")
MsgBox Suchzelle
   
  If Suchzelle <> "" Then

Kdno = WorksheetFunction.Index(Worksheets("Kunden").Range("A2:B9"), _
WorksheetFunction.Match(Worksheets("Kunden").Range("G3").Value, Worksheets("Kunden").Range("B2:B9"), 1), 1)
MsgBox Kdno
  End If
 
End Sub


Gibt es (wie in Access ) eine ARt ParameterAbfrage über eine Messagebox, in die ich den Name (statt Suchzelle) eintragen kann?

Gruß Johanna

[Flotter Feger]

Hallo,

der Ansatz die Formel in VBA zu übersetzen, ist so was von grottenfalsch ...

Für so eine Suche gibt es in VBA Range.Find() und Range.FindNext() ... es könnte ja mehrere Ergebnisse geben.
Das entspricht übrigens Strg+F und 'Weiter suchen...' ... die Zelle links daneben ist dann rng.Offset(0, -1) ... nur mal so.

Sabina

[HH-Joh.]

Hallo FlotterFeger,
danke für deine Antwort, warum ist es denn so grottenfalsch?

Code: Alles auswählen

Sub Kunde()

Dim Kdno As String
Dim Suchzelle As String
Dim Frage As String

Frage = InputBox("Bitte geben Sie eine Wert ein", "Werte-Eingabe", , 200, 500)
MsgBox Frage
Range("G3") = Frage

Suchzelle = Worksheets("Kunden").Range("G3")
MsgBox Suchzelle
   
  If Suchzelle <> "" Then

Kdno = WorksheetFunction.Index(Worksheets("Kunden").Range("A2:B9"), _
WorksheetFunction.Match(Worksheets("Kunden").Range("G3").Value, Worksheets("Kunden").Range("B2:B9"), 1), 1)
MsgBox Kdno
  End If
 
End Sub


[Flotter Feger]

... warum wohl ...

1) Range.Find ist das schnellste, was ich in VBA je gesehen habe
2) warum soll man eine Formel zusammenschustern, die einem auch noch einen LFZ um die Ohren haut, wenn es den Namen nicht gibt, den man sucht,
3) wenn es etwas gibt, das besser ist ... und auch mehrere Ergebnisse verarbeiten kann ... und nicht nur das erste der Möglichen ...

... ist ja auch nicht mein Problem ...
Only my two Cents
Sabina

[HH-Joh.]

Danke Sabina,

hab jetzt versucht, deine Idee umzusetzen.
aber es gibt einen Fehler mit der Schleife.

Code: Alles auswählen

Sub KundenFinden()
Dim Suchzelle As String
Dim Kdno As String
Dim rngFound As Range
Dim firstAddress As String

Dim i As Long

Suchzelle = Worksheets("Kunden").Range("G3").Value
'Frage = InputBox("Bitte geben Sie eine Wert ein", "Werte-Eingabe", , 200, 500)
'MsgBox Frage
MsgBox Suchzelle
 
With Worksheets("Kunden").Range("b2:B9")

Set rngFound = .Find(What:=Suchzelle, LookIn:=xlValues)
      If rngFound Is Nothing Then
        MsgBox "Suche war ergebnislos"
    Else
        firstAddress = rngFound
        Kdno = rngFound.Offset(, -1).Value
        MsgBox Kdno
  Do
    Set rngFound = .FindNext(rngFound)
    Loop While rngFound.Address <> firstAddress

    End If
  End With

End Sub

Habt ihr noch einen Tipp für mich?

Gruß
Johanna

[HH-Joh.]

habt ihr noch einen Tipp für mich ,wie ich das FindNext in einer Schleife fortsetzen kann?

Gruß
Johanna

[HKindler]

Hi,

was fällt dir bei

Code: Alles auswählen

firstAddress = rngFound

auf?

[HH-Joh.]

das sind unterschiedliche Typen string und range

Code: Alles auswählen

im Suchzelle As String
Dim Kdno As String
Dim rngFound As Range
Dim firstAddress As Variant

Suchzelle = Worksheets("Kunden").Range("G3").Value
'Frage = InputBox("Bitte geben Sie einen Wert ein", "Werte-Eingabe", , 200, 500)
'MsgBox Frage
MsgBox Suchzelle
 
With Worksheets("Kunden").Range("a1:B9")

Set rngFound = .Find(What:=Suchzelle, LookIn:=xlValues)
   
  If rngFound Is Nothing Then
        MsgBox "Suche war ergebnislos"
    Else
        firstAddress = rngFound.Address
    Do
      Set rngFound = .FindNext(rngFound)
      Kdno = rngFound.Offset(, -1).Value
      MsgBox Kdno
     Loop While rngFound.Address <> firstAddress
  End If
  End With
End Sub

So funktioniert der Code, aber nicht gut. Bei z.b. Drei Meiers findet er den Zweiten, den dritten und den ersten.

Wie mache ich es, dass er beim ersten anfängt?

[HKindler]

Hi,

das sind unterschiedliche Typen string und range

richtig. Und was macht die Anweisung firstAddress = rngFound? Richtig, sie weist der Variabeln firstAddress den Inhalt (=Wert) von rngFound zu und nicht die Zelladresse. Aber das hast du ja mittlerweile korrigiert.

Wie mache ich es, dass er beim ersten anfängt?

Überlegen wir einmal: Zunächst wird der erste Treffer gesucht. Falls dieser gefunden wurde, wird die Schleife gestartet, die den nächsten Treffer sucht. Dieser wird ausgegeben und falls die Adresse ungleich der ersten Adresse ist, wird die Schleife wiederholt. Fällt dir etwas auf? Auf diese Art gibst den 2., 3., 4.,.... 1. Treffer aus.
Was kann man dagegen tun? Ganz einfach: dreh diese beiden Zeilen um:

Code: Alles auswählen

      Set rngFound = .FindNext(rngFound)
      Kdno = rngFound.Offset(, -1).Value

wird zu

Code: Alles auswählen

      Kdno = rngFound.Offset(, -1).Value
      Set rngFound = .FindNext(rngFound)