VBA für index-vergleich

Moderator: ModerationP

VBA für index-vergleich

Beitragvon HH-Joh. » 01. Dez 2020, 14:32

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
Zuletzt geändert von HH-Joh. am 01. Dez 2020, 14:58, insgesamt 1-mal geändert.
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon Hilfloser Bastler » 01. Dez 2020, 14:41

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
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Selbst Heiden glauben an min. 10% Mystik in der EDV! Gilt auch für mein Win 10 pro/Office 2019 ProPlus und alle VBAsteleien ...
Inzwischen finde ich meinen Nutzernamen ziemlich doof. Denn eines ist sicher: Hilflos bleibt man hier nicht - Dankeschön!
Benutzeravatar
Hilfloser Bastler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 380
Registriert: 15. Jan 2015, 16:13
Wohnort: VBAsteln makes the world go around

Re: VBA für index-vergleich

Beitragvon HKindler » 01. Dez 2020, 14:59

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.
Zuletzt geändert von HKindler am 01. Dez 2020, 15:35, insgesamt 1-mal geändert.
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5611
Registriert: 04. Jul 2013, 09:02

Re: VBA für index-vergleich

Beitragvon Hilfloser Bastler » 01. Dez 2020, 15:27

Helmut's Tipp ist unangefochten viel besser! DANKE SCHÖN!
Selbst Heiden glauben an min. 10% Mystik in der EDV! Gilt auch für mein Win 10 pro/Office 2019 ProPlus und alle VBAsteleien ...
Inzwischen finde ich meinen Nutzernamen ziemlich doof. Denn eines ist sicher: Hilflos bleibt man hier nicht - Dankeschön!
Benutzeravatar
Hilfloser Bastler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 380
Registriert: 15. Jan 2015, 16:13
Wohnort: VBAsteln makes the world go around

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 01. Dez 2020, 16:10

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
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon HKindler » 01. Dez 2020, 16:28

Hi,

schreib einmal vor das "Match" noch ein "WorksheetFunction."
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5611
Registriert: 04. Jul 2013, 09:02

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 01. Dez 2020, 16:44

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
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon Flotter Feger » 01. Dez 2020, 16:44

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
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3025
Registriert: 24. Okt 2016, 16:40

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 01. Dez 2020, 16:59

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
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon Flotter Feger » 01. Dez 2020, 18:09

... 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
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3025
Registriert: 24. Okt 2016, 16:40

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 01. Dez 2020, 18:12

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
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 02. Dez 2020, 20:25

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

Gruß
Johanna
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon HKindler » 02. Dez 2020, 20:49

Hi,

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

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5611
Registriert: 04. Jul 2013, 09:02

Re: VBA für index-vergleich

Beitragvon HH-Joh. » 02. Dez 2020, 21:13

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?
_________________________________________________________________________________
Win 10 - Office 365 -
HH-Joh.
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 133
Registriert: 15. Mai 2015, 08:55

Re: VBA für index-vergleich

Beitragvon HKindler » 03. Dez 2020, 08:30

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)
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5611
Registriert: 04. Jul 2013, 09:02

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Klaus-Dieter, Paulemann, silangan und 10 Gäste