Seite 1 von 2

VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 14:32
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 14:41
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 14:59
von 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.

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 15:27
von Hilfloser Bastler
Helmut's Tipp ist unangefochten viel besser! DANKE SCHÖN!

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 16:10
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 16:28
von HKindler
Hi,

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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 16:44
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 16:44
von Flotter Feger
Hallo,

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

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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 16:59
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 18:09
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 01. Dez 2020, 18:12
von 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

Re: VBA für index-vergleich

BeitragVerfasst: 02. Dez 2020, 20:25
von HH-Joh.
habt ihr noch einen Tipp für mich ,wie ich das FindNext in einer Schleife fortsetzen kann?

Gruß
Johanna

Re: VBA für index-vergleich

BeitragVerfasst: 02. Dez 2020, 20:49
von HKindler
Hi,

was fällt dir bei
Code: Alles auswählen
firstAddress = rngFound
auf?

Re: VBA für index-vergleich

BeitragVerfasst: 02. Dez 2020, 21:13
von 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?

Re: VBA für index-vergleich

BeitragVerfasst: 03. Dez 2020, 08:30
von HKindler
Hi,
HH-Joh. hat geschrieben: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.

HH-Joh. hat geschrieben: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)