Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wert in Zeile suchen und X-mal kopieren

Wert in Zeile suchen und X-mal kopieren
18.04.2016 10:45:42
Stephan
Guten morgen Excelexperten,
ich versuche gerade einen Cash-flow mit Hilfe von VBA zu automatisieren.
Ich habe einen Input-Sheet, wo der Wert einmal eingetragen wird. Z.B. 1000€ Gewinn pro Jahr. Zudem wird der Startwert z.B. das Jahr 2020 und die Laufzeit angegeben z.B. 20 Jahre.
Was ich schon geschafft habe:
Mit Hilfe von einem WVerweis in VBA, den Wert in der Tabelle zu suchen und mir in mein Kalkulations-Sheet in einem Zeitstrahl(2000-2100) einzutragen.
Jetzt müsste ich die Zeile absuchen lassen. Wenn die Zelle nicht leer ist, soll der Wert makiert werden und X-mal (die variable Laufzeit)nach rechts kopiert werden. Leider reichen meine VBA Kenntnisse nicht aus um das umzusetzten.
Ich hoffe ich versteht was suche und könnt mir helfen :)
LG Stephan

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 11:48:58
Stephan
Hallo Chris,
dein Vorschlag klappt gut, aber ich habe mehrere jährliche Ein- und Auszahlungen von unterschiedichen Parametern. So müsste man händisch immer für jede Zeile unter dem Zeitstrahl den richtigen Zellenbezug definieren. Deswegen dachte ich, dass es mit VBA sicherlich eine schnellere und effizentere alternative gibt.
LG Stephan

AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 12:22:32
Stephan
Außerdem wird die Laufzeit bei einigen Inputs noch in 3 Terms aufgeteil die auch variable gehalten werden sollen...

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 12:42:51
ChrisL
Hi Stephan
Ich überlasse die Beantwortung deiner Frage jemandem mit hellseherischen Fähigkeiten...
cu
Chris

AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 12:53:59
Stephan
Hi Chris,
ich habe am Anfang nur versucht mein Problem möglichst einfach und verständlich zu erklären, wenn ich den angefragten Schritt hätte, glaube ich, dass ich den Rest alleine schaffen würde :)
Mit deiner Formel geht es auch wenn ich es so einfach halte aber es wird komplexer ;)
LG
Stephan

AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 12:59:38
ChrisL
Hi Stephan
Sorry, muss das "Täglich ruft das Murmeltier" Syndrom sein. Zeig doch einfach mal eine einfache Beispieldatei.
cu
Chris

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 14:07:38
Stephan
Hi Chris,
hier ein Beispiel:
https://www.herber.de/bbs/user/105052.xlsx
so würde Beispielweise der input aussehen. Daraus sollte jetzt auf dem zweiten Sheet der Cashflow automatisiert dargestellt werden.
Nur wird der Input nicht nur aus 3, wie im Beispiel, sondern bis zu 30 einzelnen Stellen bestehen. Diese sollen dann Zusammengefasst werden unter einem Zeitstrahl mit den Jahren 2000-2100. Sodass man für alle Stellen jedes Jahr die Gesamtkosten und Erträge ermitteln kann.
Danke für deine Mühe! :)
LG
Stephan

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 14:53:40
ChrisL
Hi Stephan
Wenn du die Tabelle transponierst (d.h. Zeilen und Spalten umdrehst), dann sind wir ja schon fast bei meinem ursprünglichen Beispiel. Was neu ist sind die 3 Terms und hierzu folgende Fragen:
Die Zahlen in B4:D6 verstehen sich pro Jahr?
Yield kann ignoriert werden?
Sind es immer 3 Terms?
Und sind die Term-Jahre (2, 12 und 11) für alle Positionen 1-30 immer gleich oder gibt es je Position unterschiedliche Terms?
cu
Chris

AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 15:01:01
Stephan
Hi Chris,
genau die Kosten sind pro Jahr und ändern sich in in den Intervallen der Terms(die für alle 1-30 Positionen gleich sind) die variable sein sollten.
Yield ist der Ertrag. Dieser ist jedes Jahr gleich und ist nicht in die Terms aufgeteilt
LG
Stephan

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 15:57:37
ChrisL
Hi Stephan
Ich glaube weiterhin, dass man dies gut auch mit Formeln lösen könnte. Da ich aber diesbezüglich nicht der Profi bin, bekommst du jetzt eine VBA Lösung :)
Sub Mach()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iSpalte As Long, iiSpalte As Long
Dim letzteZeile As Long
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
WS2.Rows("2:10000").Delete
For iSpalte = 2 To WS1.Cells(1, WS1.Columns.Count).End(xlToLeft).Column
letzteZeile = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(letzteZeile, 1) = WS1.Cells(1, iSpalte)
For iiSpalte = 2 To WS2.Cells(1, WS2.Columns.Count).End(xlToLeft).Column
If WS2.Cells(1, iiSpalte) >= WS1.Cells(2, iSpalte) And _
WS2.Cells(1, iiSpalte) 

In Tabelle 2, Zeile 1, ab Spalte B stehen die Jahreszahlen z.B. B1 = 2010, C1 = 2011 usw.
Anschliessen Makro laufen lassen.
cu
Chris

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 16:22:05
Stephan
Danke Chris!
es funktioniert wunderbar aber ich habe keine Ahnung wie. Würde es dir etwas ausmachen kurz zu beschreiben wie du dort was gemacht hast, damit ich deine Schritte nachvollziehen und weiter anwenden kann?
LG
Stephan

AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 16:46:57
ChrisL
Hi Stephan
Freut mich, dass es passt. Schwer zu erklären, wenn du keine VBA-Vorkenntnisse hast ;)
For-Next Schleife für die Spalten in Tabelle1 d.h. B1 (1), C1 (2), D1 (3)... werden der Reihe nach durchlaufen.
Innerhalb der For-Next Schleife gibt es eine weitere Schleife, welche die Spalten auf Tabelle2 durchläuft d.h. B1 (2010), C1 (2011), D1 (2012)...
Angenommen wir sind auf Tabelle1 in Spalte B (Position 1) und in Tabelle2 in Spalte mit Jahreszahl 2020.
Wenn 2020 erfüllt
Wenn 2020 erfüllt es wird überschrieben
Wenn 2020 nicht erfüllt
cu
Chris

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 17:35:04
Stephan
Hallo Chris,
Danke! Verstanden habe ich es jetzt. Trotzdem ist es mir noch nicht möglich die Sachen anzuwenden.
Kann ich diese Schleife auch auf beliebige Bereiche einsetzten? Wenn ja wie?
Sagen wir es würde 30 Eingangswerte geben die Zeit in dem die Kosten anfangen würden in Tabelle 1 ( D13:AG13)stehen. Die verschiedenen Werte für Term 1,2,3 stehen in D27,29,31 bis AG27,29,31
Definiert sind Intervalle von den Terms 1,2,3 in D42,D43,D44.
Der Ausgabebereich in Tabelle 2 soll erst bei C37 bis C66 beginnen. Ist das noch möglich?
LG und Danke du hilfst mir wirklich sehr :)
Stephan

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
18.04.2016 19:04:07
ChrisL
Hi Stephan
Zellen werden immer entweder mit
Range("A1")
oder
Cells(Zeile, Spalte)
angesprochen
Range müsste klar sein, B10+B11+B12 in D42,D43,D44 ändern.
Cells, immer wenn eine fixe Zahl da steht prüfen resp. ändern. Variablen kannst du stehen lassen.
Z.B. Cells(1, ...) bisher Zeile 1, neue Zeile 13 Cells(13, ...).
dito mit den Spalten Cells(..., 1)
Vielleicht klappts, sonst lade nochmal die Datei hoch, dann muss ich sie nicht nachbauen ;)
cu
Chris

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 10:52:14
Stephan
Hallo Chris,
jetzt klappt alles wunderbar bis auf eine Sache. Ich habe mich komplett reingearbeitet und konnte alles bis auf eine kleine Sache lösen. Nämlich soll der Zeitstrahl in Zeile 4 sein und die Daten erst in Zeile 37 bis 66 reingeschrieben werden. Leider verspringt mir das immer, da ich die letzte Zeile mit dem Rows.Count definiere. Gibt es da auch eine andere Lösung?
So sieht jetzt mein Code aus.
Sub Datenübertragung()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iSpalte As Long, iiSpalte As Long
Dim letzteZeile As Long
Set WS1 = Worksheets("Input")
Set WS2 = Worksheets("Monte Carlo Simulation")
For iSpalte = 4 To WS1.Cells(10, WS1.Columns.Count).End(xlToLeft).Column
letzteZeile = WS2.Cells(WS2.Rows.Count, 2).End(xlUp).Row + 1
For iiSpalte = 2 To WS2.Cells(4, WS2.Columns.Count).End(xlToLeft).Column
If WS2.Cells(4, iiSpalte) >= WS1.Cells(12, iSpalte) And _
WS2.Cells(4, iiSpalte) 
LG
Stephan

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 11:22:49
ChrisL
Hi Stephan
Versuch mal...
letzteZeile = WS2.Cells(WS2.Rows.Count, 2).End(xlUp).Row + 1
If letzteZeile < 37 Then letzteZeile = 37
cu
Chris

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 11:42:50
Stephan
Chris du bist ein Held! :D
Vielen lieben Dank!

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 11:51:07
Stephan
Ah schade ich dachte das wär es...
Aber in den Zeilen 6-35 und ab 68 stehen auch wieder Zahlen drin. Ich glaube das mit dem Zählen geht dann nicht mehr oder? Kann ich ihm nicht sagen fang bei Zeile 37 Spalte 2 an und geh dann immer eine Zeile tiefer? Sowas wie x = Anzahl an durchläufen der äußeren Schleife und Zeile ist dann 37+x oder so? :)
LG Stephan

Anzeige
AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 12:53:03
ChrisL
Hi Stephan
Lade bitte noch einmal eine Beispieldatei hoch. Ich kann es so nicht nachvollziehen.
cu
Chris

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 13:53:21
Stephan
Hallo Chris,
hier die Datei:
https://www.herber.de/bbs/user/105078.xlsx
Es geht um die die Zeiles des Tarifes, die übertragen werden sollen.
Die Tabelle wird aber noch weiter nach unten ausgebaut.
LG
Stephan

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 14:07:22
ChrisL
Hi Stephan
OK du hast zwar alles richtig beschrieben, aber manchmal versteht man es halt erst wenn man es sieht.
Sub Datenübertragung()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim iSpalte As Long, iiSpalte As Long
Dim letzteZeile As Long
Set WS1 = Worksheets("Tabelle1")
Set WS2 = Worksheets("Tabelle2")
letzteZeile = 36
For iSpalte = 4 To WS1.Cells(10, WS1.Columns.Count).End(xlToLeft).Column
letzteZeile = letzteZeile + 1
For iiSpalte = 2 To WS2.Cells(4, WS2.Columns.Count).End(xlToLeft).Column
If WS2.Cells(4, iiSpalte) >= WS1.Cells(12, iSpalte) And _
WS2.Cells(4, iiSpalte) 

ciux
Chris

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 14:11:33
Stephan
einfach und genial!
hätte man auch selber drauf kommen können sorry :D
Danke!
Wenn ich weite kleine Fragen habe kann ich die dann einfach hier unter dem Threat stellen oder lieber einen Neuen eröffnen? Weil mit dem Ursprung hatte das ja jetzt nicht mehr viel zu tun.
LG
Stephan

AW: Wert in Zeile suchen und X-mal kopieren
19.04.2016 14:30:59
ChrisL
hi Stephan
Meist beobachte ich "meine" Beiträge noch ein wenig, aber nach ca. einer Woche fliegen sie raus.
Da der Beitrag aber schon ziemlich lang ist, wäre vielleicht ein neuer angebracht. In solchen Fällen wird ein Link auf den ursprünglichen Beitrag sehr geschätzt.
cu
Chris

332 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige