Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Index Vergleich als VBA

Betrifft: Index Vergleich als VBA von: Steve
Geschrieben am: 26.03.2020 17:23:01

Moin Leute,

ich knabber da gerade an einenm Problem. Ich habe zwei Listen. Die eine ist chaotisch und unsortiert und die andere ist sortiert bzw. soll es sein.

Beide Listen sind immer in den ersten beiden Spalten gleich. (Nummer und Name)

Die chaotische erste Liste (LISTE)
enthält immer einen zweierblock an Daten z.B. Abfrage1 und Ergebnis1
Der Inhalt dieser zwei Zellen ist aber von Zeile zu Zeile unterschiedlich. So kann bei der ersten Person als erster Eintrag Hammer / 1 stehen, bei der zweiten Person steht Schraubenschlüssel / 2
USW.
- Alle Einträge in LISTE entstehen automatisch über DieseArbeitsmappe

Die zweite Liste (INFO)
muss sortiert sein. Da steht dann als Überschrift "Hammer" und als Ergebnis bei Person 1 soll dann nur die Menge stehen; also 1. usw.

Oft ist es so, das Liste 1 mehr Einträge hat als Liste 2 weil nicht alles für die sortierte Liste von relevanz ist.
Das kann sich aber unter Umständen auch ändern. Deshalb müsste das Makro (wahrscheinlich anhand der Überschriften) von selbst das horizontale Listenende finden.

Ich habe lediglich eine Formelumsetzung mit INDEX;VERGLEICH;VERGLEICH hinbekommen. Aber da sich die Liste sowohl in der Länge wie auch in der Breite ständig ändert, müsste ich diese immer wieder neu anpassen.

Ich muss leider gestehen, ich habe keine Ahnung wie ich das bewerkstelligen soll, denn dafür reichen meine VBA Kenntnise nicht aus.Vielleicht kann mir da jemand sagen wie ich da vorgehen muss.

Ich habe mal meine Datei vorbereitet, so dass das Problem hoffentlich gut ersichtlich ist. Wie schon gesagt habe ich eine Formellösung gefunden aber die würde auf lange Sicht nur arbeit machen, weil die Tabelle ja ständig verändert.

https://www.herber.de/bbs/user/136130.zip

Liebe Grüße

Steve

Betrifft: AW: zunächst mal ...
von: neopa C
Geschrieben am: 27.03.2020 20:16:21

Hallo Steve,

... schlage ich vor, dass Du in einer Kopie Deiner derzeitigen Datei diese Dich beschränkst auf die Beispieldatenliste und dazu "händisch" zusammengestellt aufzeigst wie Deine "Ziellisten" aussehen sollen.
Die Datenliste solltest Du als Tabelle Formatieren und angegeben, mit wie vielen Personen und mit wie viele Zweier Blöcke in Deiner Auswertung zu rechnen ist. Dann schau ich es mir am WE noch einmal an.

Gruß Werner
.. , - ...

Betrifft: AW: zunächst mal ...
von: Steve
Geschrieben am: 28.03.2020 23:05:56

Moin Werner,

Ich danke dir für dein Hilfeangebot. Das Problem ist, das die listen sich ja quasi von selbst erstellen, deshalb ist meine beispieldatei eigentlich das beste Beispiel.

Zur Erläuterung: mit jeder neuen Folie wird ein neuer Eintrag erstellt. Das geschieht auf beiden listen automatisch.
Die Dimension der ersten liste (Liste) hat der user beim einrichten festgelegt und variiert je nach Verwendung dieser Datei.
Diese ist deshalb chaotisch, weil der user diese für Serienbriefe verwenden kann.

Ebenso die zweite liste (Info) legt er auch selber fest, und dient der schnellen Übersicht. Deshalb kann sich diese Liste je nach Verwendungszweck immer verändern.

Aus diesem Grund kann ich dir keine Konkrete beispieldatei senden.

Wenn du aber in meiner Datei auf NEU klickst, dann wirst du sehen, dass ein neuer Eintrag in beiden Listen erstellt wurde.
In dem neuen Eintrag könnte man nun in den blauen Feldern (wovon der User beim einrichten des Layouts beliebig viele erstellen kann - jedes der blauen Felder findet sich dann wiederum in LISTE wieder) etwas eintragen. Z.B. Hammer und daneben eine 1

Über MASTER auf der Startseite wäre es möglich das Layout zu verändern und diese blauen Felder in beliebiger Zahl hinzuzufügen. Diese sind bestandteil der dynamischen Tabelle links und können verschoben und erweitert werden.

In INFO wiederum stehen derzeit nur händisch die Formeln drin die dem User auf einen Blick zeigen wer alles einen Hammer hat. (Hammer ist natürlich nur ein Beispiel) (Bisher habe ich es geschafft meiner Datei ein extrem hohes Maas an Flexibilität zu halten, damit diese Datei für viele Dinge verwendet werden kann. )

Mein Gedanke war die Formel in der Liste aus „INFO“ welche derzeit händisch notiert ist so in VBA umzusetzen, das sie von selber merkt wo die dynamische Tabelle endet. Sowohl nach unten, wie auch nach rechts.
Das rechte Ende könnte man ja über die Überschrift finden und das linke über die Nr (diese wird immer mit dem anlegen einer neuen Folie automatisch mitangelegt und ist damit eine fixe größe.

Mit anderen Worten, es ist offen wieviele zweierblöcke und wieviele Personen angelegt werden.

Sollte das ohne weiteres nicht möglich sein, dann habe ich eine andere Frage.
Nach unten, also was die Personen angeht, verändert sich die Datei häufig, aber der Vorteil der Intelligenten Tabelle ist, das sie die Formeln brav mit nach unten zieht.

Nach rechts verändert sich die Tabelle nicht so häufig, eher nur dann wenn jemand sich denkt er müsse mal eine andere Auswertung fahren. (Vielleicht möchte er ja nicht mehr wissen wer alles einen Hammer hat, sondern nun wer alles einen Schraubendreher und ein Cutter hat) Nach rechts könnte der User die Formel im Notfall selber ziehen
(Wobei es dann wohl möglich wäre diese Formel auch zu zerstören, was ich bisher versuche zu vermeiden.)

ABER wenn ich jede Person Lösche, dann löschen sich auch die Zeileneinträge der Person. Das ist so gut und gewollt.
Lösche ich aber die letzte Person, dann sind auch alle Formeln raus. Das wäre ungeschickt. Die Notlösung wäre also zumindest nach dem Löschen der letzten Person die Formel in die erste Zeile (welche dann ja zunächst leer ist) wieder einzutragen.

PS.: Ich hatte auch versucht die Formeln einfach für eine sagen wir mal adäquate Breite und Länge vorzubereiten, aber dann ändert sich ja leider auch die Größe der intelligenten Tabelle. Diese möchte ich aber nicht weglassen, weil ich mir bzw. dem User dann die Möglichkeit von PIVOT verbaue.

Okay, das war viel Text von mir. Ich hoffe es war zumindest brauchbarer Text.
Ich bastel schon so lange an der Datei und habe viel dabei gelernt. Leider habe ich dafür auch nach reichlicher Recherche keine Lösung gefunden.

Liebe Grüße

Steve

Betrifft: AW: ich kennzeichne den thread als offen owT
von: neopa C
Geschrieben am: 29.03.2020 13:57:43

Gruß Werner
.. , - ...

Betrifft: vielleicht war die Frage zu kompliziert gestellt..
von: Steve
Geschrieben am: 30.03.2020 15:49:23

Ich möchte es noch einmal versuchen.

Ich möchte in einer intelligenten Tabelle folgende Formel unterbringen:

=WENNNV(WENN($B2<>"";(INDEX(Liste1[#Alle];VERGLEICH($B2;N_LISTENR;0);VERGLEICH(D$1;Liste1[@];0)+1) );"");"")

Jetzt ist es so, das sich die intelligente Tabelle sowohl nach unten, wie auch nach rechts ständig verändert und die Änderung nicht absehbar ist. Ausserdem werden die erste und die zweite Spalte dieser Tabelle von selbst befüllt.

Entsprechend muss die Formel in der 3 Spalte und zweiten Zeile beginnen. Das wäre hier D2.

wie schaffe ich es diese Formel so in VBA unterzubringen, das jede benutzte Spalte wie auch Zeile diese Formel enthält?

Kann mir da jemand bei helfen? Gerne auch zum Selber nachlesen.

Liebe Grüße

Steve

Betrifft: erster versuch einer eigenen Lösung
von: Steve
Geschrieben am: 31.03.2020 16:53:27

Moin,

ich habe mal versucht mein Problem in kleinere Schritte zu zerlegen.

1. Makro für letzte benutzte Zelle

da habe ich mir nach einiger Recherche was zusammengebastelt und das sieht so aus.
Sub letztspadr2()

Dim lngSp As Long
Dim strAdr As String
Dim lngCnt As Long
Dim strErg As String    'der Spaltenbuchstabe
Dim lngZeile As Long, lngSpalte As Long
Dim ladr As String


lngZeile = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
lngSpalte = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lngSp = lngSpalte
strAdr = ActiveSheet.Cells(1, lngSp).Address
lngCnt = InStr(2, strAdr, "$") - 1
strErg = WorksheetFunction.Substitute(Left(strAdr, Len(strAdr) - (Len(strAdr) - lngCnt)), "$", " _
")
ladr = strErg & lngZeile

MsgBox "Adresse: " & ladr
 
End Sub
2. Jetzt möchte ich das ganze gerne auf eine intelligente Tabelle beschränken. Dazu finde ich aber nichts im Netz.

Habe mir gedacht ich könnte ActiveSheet einfach mit Range("Test") ((so heisst die Tabelle)) tauschen. Aber das ging leider nicht.

3. Formel in die zweite Zeile und dritte Spalte der Tabelle übertragen -> fehlt mir noch
4. Formel auf den ganzen Bereich ausweiten. -> fehlt mir noch


Kann mir da jemand vielleicht entweder sagen wo ich was finde oder mir einen Tipp geben?

Liebe Grüße

Steve

Betrifft: HEUREKA.....Fast geschafft
von: Steve
Geschrieben am: 31.03.2020 18:26:02


Nachdem ich so fünf Stunden rumgebastelt habe, habe ich nun eine Lösung die funktioniert.
Zwar noch nicht ganz so wie ich möchte, aber brauchbar.

Ich habe es geschafft, das die Formel:
=WENNNV(WENN($B2<>"";(INDEX(Liste1[#Alle];VERGLEICH($B2;N_LISTENR;0);VERGLEICH(D$1;Liste1[@];0)+1));"");"")

in die relevanten Zellen eingefügt wird. Ich habe es sogar hinbekommen, das nur das Ergebnis eingegeben wird.
Sub letztspadr2()

Dim lngSp As Long
Dim strAdr As String
Dim lngCnt As Long
Dim strErg As String    'der Spaltenbuchstabe
Dim lngZeile As Long, lngSpalte As Long
Dim ladr As String

lngZeile = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
lngSpalte = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lngSp = lngSpalte
strAdr = ActiveSheet.Cells(1, lngSp).Address
lngCnt = InStr(2, strAdr, "$") - 1
strErg = WorksheetFunction.Substitute(Left(strAdr, Len(strAdr) - (Len(strAdr) - lngCnt)), "$", " _
")
ladr = strErg & lngZeile

MsgBox "Adresse: " & ladr
 
With Range("D2:" & ladr)
.Formula2R1C1 = "=IFNA(IF(RC2<>"""",(INDEX(Liste1[#All],MATCH(RC2,N_LISTENR,0),MATCH(R1C,Liste1[ _
@],0)+1)),""""),"""")"
End With

Range("D2:" & ladr) = Range("D2:" & ladr).Value


End Sub
Allerdings habe ich es noch nicht geschafft das Makro auf die intelligente Tabelle zu beschränken.
Und ich habe keine Ahnung wie ich das alles so unterbringe, dass das Makro von selbst anspringt sobald die Tabelle sich verändert (egal ob Zeile oder Spalte.)

Das ist auf jeden Fall mein Ergebnis. Wäre schön jemand könnte sich das mal anschauen und mir sagen ob ich das so lassen kann.
Und vielleicht kann mir auch jemand ein Tipp geben wie ich die zwei ungelösten Probleme umsetzen kann.

Liebe Grüße

Steve

Beiträge aus dem Excel-Forum zum Thema "Index Vergleich als VBA"