Office: (Office 2016) Makro für variable Anzahl von Datensätzen

Hallo zusammen,

ich habe wieder einmal ein kleines Excel-Problem, bei dem Ihr mir sicherlich helfen könnt.

Ich habe eine Excel-Liste, das ist ein Export aus einem anderen Programm (siehe Beispiel). Diese Liste enthält eine variable Anzahl von Datensätzen.

In diese Liste sollen nun mit Hilfe eines Makros und dem SVERWEIS (in die Spalten I und P) Daten aus einem anderen Tabellenblatt zugeordnet werden. Soweit so gut.

Da die Tabelle aber immer eine andere Anzahl an Datensätzen hat, möchte ich nun, dass das Makro immer bis zum Ende der Tabelle geht.

Gibt es eine Möglichkeit über eine Abfrage dem Makro mitzuteilen, wie weit er die Formeln runterkopieren soll?

Also man startet das Makro, dann kommt ein Fenster „Wie viele Datensätze hat der Export?“ … nun gibt man 62 an (für das aktuelle Beispiel) und der schreibt die Formeln in die Zellen I8:I70 und P8:P70.

Alternativ könnte man dem Makro ohne Abfrage sagen: schreibe die Formel von I8 bis zur letzten Zellen mit Umrandung?

Vielen Dank schon einmal im Voraus.
Micha

 

Hi Micha,

weshalb schreibst du deine SVERWEIS-Formel nicht einfach so, dass sie nur dann die Werte ausgibt, wenn ich Spalte S tatsächlich Werte vorhanden sind? In I8 =WENNFEHLER(SVERWEIS(S8;T:U;2;);"") und P8 =WENNFEHLER(SVERWEIS(S8;T:V;3;);"")

---

 

Hallo Karin,

die Idee hatte ich auch schon, nur wenn ich das über ein Makro laufen lassen und das Makro bis zu Zeile 100 laufen lasse (da ich die Höchstzahl der Datensätze nicht kenne), gibt es in der Spalte I Probleme ab Zeile 80, da dort das Makro auf verbundene Zellen trifft. In der Spalte P die selben Probleme ab Zeile 74.

Ich hatte vorhin eine Datei ohne Makro angefügt, die jetzt ist mit Makro (hatte vorhin die falsche Datei gewählt).

Range("I8").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[10],C[11]:C[13],2,)"
Range("I8").Select
Selection.Copy
Range("I9:I70").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Ist es möglich in "I70" durch einen variablen Wert zu ersetzen?

Viele Grüße
Micha

 

Hi Micha,

wie sieht denn deine Tabelle aus, wenn mehr Daten als bis Zeile 70 vorhanden sind - schieben sich deine verbundenen Zellen dann nach unten oder bleiben sie stehts im Bereich der Zeilen 74 bis 79 und die Tabelle geht unterhalb weiter?

---

 

Nein, dass was unter der Tabelle steht (also momentan Zeile 72 bis 79) steht immer am Ende. Bedeutet, wenn die Tabelle bis Zeile 80 geht, wären die verbundenen Zellen dann in der Zeile 82 bis 89. Wenn die Tabelle nur bis Zeile 60 geht, dann sind die verbundenen Zellen schon in Zeile 62 bis 69 usw.

 

Der Begriff "Summe" steht anscheinend immer in Spalte B 2 Zeilen unterhalb der letzten Daten-Zeile - folglich kann man diesen Begriff suchen, 2 Zeilen nach oben gehen und schon hat man die letzte Zeile bis zu welcher die Daten gehen:

Code:

Sub FormelnEintragen()
    Dim rngSumme As Range
    Set rngSumme = Columns(2).Find("Summe", lookat:=xlWhole)
    If Not rngSumme Is Nothing Then
        Range("I8").Formula = "=VLOOKUP(S8,T:V,2,)"
        Range("I8").AutoFill Destination:=Range(Cells(8, 9), Cells(rngSumme.Row - 2, 9))
        Range("P8").Formula = "=VLOOKUP(S8,T:V,3,)"
        Range("P8").AutoFill Destination:=Range(Cells(8, 16), Cells(rngSumme.Row - 2, 16))
    End If
End Sub

Noch ein Hinweis: verbundene Zellen sollte man tunlichst vermeiden, denn sie bereiten IMMER Problem - egal ob bei der VBA-Programmierung oder wenn man den Aufbau eines Tabellenblatt später einmal ändern muss. Es gibt die verschiedensten ANDEREN Möglichkeiten zur optischen Gestaltung, sodass man komplett ohne verbundene Zellen auskommen kann...

---

 

Hallo msdd79,

also, da es sich offenbar um eine Art Formular handelt, sehe ich mal darüber hinweg, dass der Listenaufbau im Gegensatz zu den meisten Excelregeln steht.
Eine Frage stellt sich allerdings: warum willst du per Makro Formeln in deine Liste schreiben? Das Makro kann das doch wunderschön berechnen und gleich die Ergebnisse eintragen. Die Ermittlung des aktuellen Listenendes kann man problemlos implementieren.

 

@Karin: vielen Dank für Deine Lösung. Ja, der Aufbau der Tabelle ist immer gleich. Ich werde deine Lösung mal testen und Dir (sicherlich ein positives) Ergebnis melden.
Bei der Formatierung der Exportliste sind mir die Hände gebunden, ich würde das nicht so formatieren :-)

@Klaus-Dieter: vielen Dank für Deine Antwort. Meine Makro-Kenntnisse kann man getrost als "Basiswissen" bezeichnen. Bedeutet, alles mit dem Rekorder aufzeichnen und dann aus vielen kleinen Makros ein umfassendes basteln in der Hoffnung, dass es geht.
Will damit sagen, für Deinen Lösungsweg habe ich leider noch nicht genug Makro-Kenntnisse

 

Um die Ergebnisse per VBA ermitteln zu können müsste man in einer Schleife alle Zeilen durchlaufen, die Daten in der laufenden Zeile aus den Spalten B, F, K, N gleich deiner Formel in Spalte S =B8&F8&K8&N8 zusammen fügen und dieses Konstrukt dann in Spalte T suchen um die zugehörigen Werte aus Spalte U bzw. V zu nehmen und in die laufende Zeile in Spalte I bzw. P eintragen. Ich bin zwar auch ein Verfechter dessen, dass man normalerweise gleich alles per VBA macht, wenn man sowieso VBA benutzt. Aber im gegegeben Fall ist der zeitliche Aufwand dafür auf keinen Fall geringer, eher wesentlich höher, als wenn du so wie bisher die Formel per VBA eintragen lässt.

---

 

Hallo Karin,

deiner letzten Aussage muss ich hier widersprechen, im meinem Makro habe ich einfach auch den SVerweis im Makro angewendet und so wie unter #7 vorgeschlagen, die Treffer direkt eingetragen, das Makro läuft, genau wie deines, unter einer Sekunde durch.

Code:

Sub Übertrag()
    Dim lngZeile As Long
    For lngZeile = 8 To Tabelle1.Range("B7").End(xlDown).Row
        Tabelle1.Cells(lngZeile, 9) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(lngZeile, 19), Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 2, False)
        Tabelle1.Cells(lngZeile, 16) = Application.WorksheetFunction.VLookup(Tabelle1.Cells(lngZeile, 19), Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 3, False)
    Next lngZeile
End Sub

Den anderen Aspekt werde ich mir noch mal ansehen.

 

Hallo zusammen,

hier nun das Makro ohne die Berechnungen in Spalte S, Laufzeit ebenfalls unter einer Sekunde:

Code:

Sub Übertrag()
     Dim lngZeile As Long
    Dim lngSpalte As Long
    Dim strSuch As String
    Dim varSpArr() As Variant
    varSpArr = Array("B", "F", "K", "N")
    For lngZeile = 8 To Tabelle1.Range("B7").End(xlDown).Row
    strSuch = ""
        For lngSpalte = 0 To 3
            strSuch = strSuch & Range(varSpArr(lngSpalte) & lngZeile)
        Next lngSpalte
        Tabelle1.Cells(lngZeile, 9) = Application.WorksheetFunction.VLookup(strSuch, Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 2, False)
        Tabelle1.Cells(lngZeile, 16) = Application.WorksheetFunction.VLookup(strSuch, Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 3, False)
    Next lngZeile
End Sub

 

Ich danke Euch beiden für Eure Lösungen, ich werde beide Wege morgen einmal testen (was nicht heißen soll, dass ich bezweifele, dass die funktionieren) und berichten.

Viele Grüße
Micha

 

Hi Klaus-Dieter,

die Schliefe über die 4 Spalten ist nicht nötig - den Suchparamater kann man direkt beim Zeilendurchlauf zusammensetzen. Dann ist auch die Zeile strSuch = "" überflüssig, weil der Suchparameter jedesmal neu erstellt wird:

Code:

Sub Übertrag()
    Dim lngZeile As Long
    Dim strSuch As String
    For lngZeile = 8 To Tabelle1.Range("B7").End(xlDown).Row
        strSuch = Tabelle1.Cells(lngZeile, 2) & Tabelle1.Cells(lngZeile, 6) & Tabelle1.Cells(lngZeile, 11) & Tabelle1.Cells(lngZeile, 14)
        Tabelle1.Cells(lngZeile, 9) = Application.VLookup(strSuch, Tabelle1.Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 2, False)
        Tabelle1.Cells(lngZeile, 16) = Application.VLookup(strSuch, Tabelle1.Range("T8:V" & Tabelle1.Range("T8").End(xlDown).Row), 3, False)
    Next lngZeile
End Sub

Dein Code für die Ermittlung der letzten zu berücksichtigenden Zeilen setzt voraus, dass es keine Leerzellen in den Bezugsspalten gibt - das sollte man aber auf jeden Fall erwähnen, wenn man mit .End(xlDown).Row arbeitet.
Wenn man auf die Zellen mittels Tabellenbezug referenziert, dann sollte man es auch an allen Stellen machen...

---

 

Hallo Karin,

viele Wege führen nach Rom. Wer weiß schon, welcher der beste ist? Per se ist eine Schleife ja erst einmal nicht schlecht, bzw. kein Fehler.

 

Gewiss, Fehler ist es keiner, aber das mit den Wegen nach Rom sollte man mit Vorsicht genießen, denn unnötige Umwege verlangsamen einen Code unnötig - insbesondere wenn es sich (irgendwann denn) um sehr große Datenmengen handeln sollte.

---