Code kommentiert...
09.01.2017 10:23:21
Michael
Hallo Jonny,
'Nutzung des Änderungs-Ereignisses auf dem betroffenen Tabellenblatt
'D.h. werden Zellen (in einem bestimmten Bereich) geändert, "feuert"
'das Ereignis und der nachfolgende Code wird ausgeführt
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim WsQ As Worksheet: Set WsQ = Wb.Worksheets("Controllingdaten")
Dim Prog$, Sp, Such As Range, rIn As Range, rOut As Range, i&
'Bestimmung der Bedingungen, ob der nachfolgende Code ausgeführt
'werden soll:
'Wenn die geänderte Zelle "D2" auf diesem Blatt ist (jenes Blatt
'in dessen Code-Modul dieser Code liegt) UND auch nur diese eine
'Zelle betroffen ist (keine Auswahl mehrerer Zellen) dann wird der
'nachfolgende Code ausgeführt
If Target.Address = "$D$2" And Target.Cells.Count = 1 Then
'Der eingegebene Zellwert wird zum Suchbegriff
Prog = Target.Value
'WSQ ist das Quellblatt (aus dem die Daten geholt werden), wie
'oben definiert, idF das Blatt "Controllingdaten" in dieser Arbeitsmappe
With WsQ
'Auf dem Quellblatt wird jener Bereich bestimmt, in dem der Suchbegriff
'gesucht wird, hier ab Zelle C3 bis zur letzten gefüllten Zelle in dieser
'Zeile (Zeile 3):
'.Cells(3,3) = Zelle C3 --> Range(Zeile, Spalte) (Spalte C = Spalte 3)
'.Cells(3,.Columns.Count).End(xltoleft) kann man sich wie das manuelle
'"Springen" mit dem Zell-Cursor in Excel vorstellen:
'Ausgehend von der letzten Spalte in Zeile 3 (.Columns.Count = als Spalten-
'Nummer wird die gesamte Anzahl an Spalten der Zeile angesetzt) springt
'Excel nun nach links zur ersten gefüllten Zelle - das ist dann quasi
'das Zeilenende in unserem Suchbereich. D.h. der Suchbereich reicht von
'Spalte 3 bis zu der Spalte dieses Spaltenendes.
Set Such = .Range(.Cells(3, 3), .Cells(3, .Columns.Count).End(xlToLeft))
'Wir suchen den Suchbegriff im Suchbereich:
'Wird der Begriff im Bereich gefunden, gibt uns diese Funktion die Position
'des Begriffs im Bereich an (!); d.h., nachdem wir hier ja in einer Zeile, aber
'in mehreren Spalten suchen, erhalten wir, wenn der Begriff gefunden wird, die
'Angabe in welcher Spalte des Bereichs der Begriff vorkommt
Sp = Application.Match(Prog, Such, 0)
'Wenn der Begriff gefunden wird, werden Daten übertragen, wenn nicht, passiert
'nichts
If Not IsError(Sp) Then
'Wir definieren den Zielbereich (in den die Daten kopiert/übertragen werden)
'Der Zielbereich liegt ja auf diesem Arbeitsblatt (jenes Blatt in dessen
'Code-Modul) dieses Makro steht - dieses Blatt lässt sich idF mit "Me"
'ansprechen... Der Zielbereich wird nun wieder über Zell-Adressen definiert:
'Zelle(3,4) = D3, Zelle(7,4) = D7
Set rIn = Me.Range(Me.Cells(3, 4), Me.Cells(7, 4))
'Aufgrund des gefundenen Suchbegriffs, der ja eine Spaltenzahl zurückgibt,
'können wir auch den Quellbereich, aus dem wir die Daten holen, eindeutig
'definieren:
'Zelle(3, Sp +2) = Zeile 3 in der Spalte, die sich wie folgt definiert:
'Ist der gefundene Begriff zB in der 3. Spalte des Suchbereichs, dann gibt
'uns die Suchfunktion "3" zurück - nachdem wir den Suchbereich aber erst ab
'Spalte 3 (siehe oben [Set Such = .Range(.Cells(3, 3),]) beginnen lassen, mü _
ssen
'wir 2 Spalten (nämlich A+B bzw. 1 + 2) zu jeder gefundenen Spaltenzahl _
hinzuaddieren,
'dass wir einen korrekten Blattbereich erhalten).
'[.Cells(3, Sp + 2)] wäre nun die Zelle im Quellbereich, die unseren _
Suchbegriff enthält,
'die interessiert uns aber inhaltlich nicht, sondern die Zahlen die in der _
Spalte darunter
'stehen, d.h. - ausgehend von der gefundenen Zelle versetzen wir die Zelle um _
eine Zeilen
'nach unten, und vergrößern dann die Auswahl auf fünf Zeilen...
Set rOut = .Cells(3, Sp + 2).Offset(1, 0).Resize(5, 1)
'...und die Werte dieser fünf Zeilen schreiben wir in die Zellen des _
Zielbereichs
For i = 1 To rIn.Cells.Count: rIn(i) = rOut(i): Next i
End If
End With
End If
End Sub
Es gibt immer verschiedene Wege, an so etwas heranzugehen; das wäre jetzt meiner. Passt?
LG
Michael