Anzeige
Archiv - Navigation
1360to1364
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

zeile suchen und mit formel kopieren

zeile suchen und mit formel kopieren
22.05.2014 15:40:01
Christian

Hallo Helfer,
Ausgangslage:
Tabellenblat mit Einträgen von Zelle Q26 bis Spalte W...
In der Tabelle stehen in der Spalte V ab Zeile 26 verschiedene Werte, in der Spalte W stehen die Summewenn() Formeln dazu.
Jetzt habe ich folgendes vor:
Wenn in Spalte V ein bestimmter Suchbegriff ("Summe....*") gefunden wird, sollte jede gefundene Zeile am Ende der Tabelle (Bereich Q.. bis W..)eingetragen werden, wenn möglich, dann in jede zweite Zeile.
Wichtig hiebei wäre, die Formeln mit den Bezügen zu übernehmen, wie sie in der Originalzeile sind.
Wie müsste der Code dazu lauten?
kleines Beispiel anbei:
Also Bereich hier im Beispiel geht bis R76, und dann sollten ab Zeile 78 nochmals die einzelnen Summe aufgelistet werden, jedoch mit den Formeln und den Bezügen in Spalte W und der Formatierung.
Bis jetzt habe ich das per Hand gemacht, dauert aber bei größeren Listen entsrechend lange.
Das einzige was ich per Vba schaffe, ist die Überschrift Zusammenstellung zu erstellen.
Für Hilfe bedanke ich mich im Voraus.
MfG Christian

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Bsp vergessen!!
22.05.2014 15:42:06
Christian
Hallo,
hier das Bsp:
AW: Bsp vergessen!!
22.05.2014 16:09:14
kimbo
Hallo Christian,
lstrow = UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 1 to lstrow
lstrowz = UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Worksheets("Tabelle1").Cells(i, 27) like "Summe*" then
Worksheets("Tabelle1").Cells(i,27).Copy
Worksheets("Tabelle1").Cells(i,lstrowz).PasteSpecial Paste:=xlPasteFormulas
End If
next i

Habs jetzt noch nicht ausprobiert, aber versuch es mal
Gruß
kimbo

AW: Bsp vergessen!!
22.05.2014 16:12:09
kimbo
Sorry, hab da was übersehen.

Sub kopieren()
lstrow = UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 1 to lstrow
lstrowz = UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Worksheets("Tabelle1").Cells(i, 27) like "Summe*" then
Worksheets("Tabelle1").Cells(i,27).Copy
Worksheets("Tabelle1").Cells(i,lstrowz + 1).PasteSpecial Paste:=xlPasteFormulas
End If
next i
end sub

Anzeige
Deinen Helferwillen in Ehren...
22.05.2014 16:39:59
EtoPHG
kimbo,
Aber was du hier im Forum für Code verschleuderst, ist jenseits von Gut und Böse ;-)
Du weisst anscheinend nicht was 'saubere' Referenzierung heisst und ich hoffe die Frager wenden deinen Code nicht unbedacht an.
Es ist keine einzige Variable dimensioniert!
UsedRange unreferenziert bezieht sich auf das gerade aktive Blatt (hoffentlich ist es eines vom Typ xlWorksheet). Zudem ist UsedRange ein mit grosser Vorsicht anzuwendendes Objekt, gerade in einer Tabelle mit unbeschriebenen Zellen!
Was soll lstrow, wenn die Variable nicht verwendet wird?
Wie kommst du darauf eine Zeilenvariable (lstrowz) als Spaltenindex einzusetzen?
Gruss Hansueli
@Christian : Ich bin noch nicht ganz durchgestiegen, was du willst, vielleicht wäre ein Beispielmappe mit Erklärungen besser, als ein Bild!
Gruess Hansueli

Anzeige
AW: Bsp vergessen!!
22.05.2014 16:44:50
Daniel
Hi
vielleicht so
Dim LetzteZeile as long
Dim Zeile1 as long
Dim Zeile2 as long
LetzteZeile = Cells(Rows.Count, "V").end(xlup).Row
Cells(LetzteZeile + 2, "S").Value = "Zusammenstellung"
Zeile2 = LetzteZeile + 4
For Zeile1 = 1 to LetzteZeile
if Cells(Zeile1, "V").value Like "Summe*" Then
Cells(Zeile2, "V").Formula = Cells(Zeile1, "V").Formula
Cells(Zeile2, "W").Formula = Cells(Zeile1, "W").Formula
Zeile2 = Zeile2 + 2
End If
Next
Gruß Daniel

Schon fast! ;-)
22.05.2014 17:08:09
Christian
Hallo Daniel,
es klappt soweit, nur dass die Formatierungen nicht übernommen werden, dass ist aus der Jeanie leider nicht abzulesen, dass die Zellen S:W unterstrichen sind, und Spalte V rechtsbündig ist. Das wird vorher per Makro festgelegt, also keine bed. Formatierung.
Kann man da noch was einbauen?
Oder ist es dann sinnvoller die Formate mit bedingter Formatierung festzulegen?
@ Hansueli und @ kimbo
Dass die Referenzierung fehlt, ist mir gleich aufgefallen, und wegen UsedRange hab ich dass auch nicht getestet, denn es sind noch mehr Einträge im Tabellenblatt, da hatte ich Bedenken dass mir mein File abstürzt.
@ Hansueli
Das mit dem Einstellen ist immer etwas schwierig, da die Mappe auch gezippt größer als hier erlaubt ist, aber Daniels Ansatz bzw. Lösung ist die Richtige Richtung.
Auf alle Fälle bedanke ich mich für eure Unterstützung!
MfG Christian

Anzeige
AW: Schon fast! ;-)
22.05.2014 17:13:21
Daniel
Hi
kann man natürlich so einbauen, beispielsweise indem du noch
ein
Cells(Zeile1, "V").Resize(, 2).Copy
Cells(Zeile2, "V").PasteSpecial xlpasteformats
einbaust, um auch die Formate zu übertragen.
im Prinzp wäre es auch denkbar, die Formeln per Copy-Paste zu übertragen.
das funktioniert aber nur, wenn du die Zellbezüge in den Originalformlen absolut setzt (mit den $-Zeichen). Da du aber wahrscheinlich mit relativen Zellbezügen arbeitest, muss der Formeltext direkt übernommen werden.
Gruß Daniel

AW: Schon fast! ;-)
22.05.2014 18:00:36
Christian
Hallo Daniel,
mit meinen spärlichen VBA Kenntnissen gelingt es mir leider nocht, Deinen Vorschlag azupassen.
Mein bisheriger Code:
Private Sub CommandButton20_Click()
'Zusammenstellung
Dim LetzteZeile As Long
Dim Zeile1 As Long
Dim Zeile2 As Long
With wksLvz
LetzteZeile = [Q:W].Find(what:="*", After:=[Q1], LookIn:=xlValues, SearchDirection:=xlPrevious). _
Row
Cells(LetzteZeile + 2, "S").Value = "Zusammenstellung"
Zeile2 = LetzteZeile + 4
Range(.Cells(LetzteZeile + 2, "S"), .Cells(LetzteZeile + 2, "W")).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
End With
With Selection
.HorizontalAlignment = xlRight
End With
For Zeile1 = 26 To LetzteZeile
If Cells(Zeile1, "V").Value Like "Summe*" Then
Cells(Zeile2, "V").Formula = Cells(Zeile1, "V").Formula
Cells(Zeile2, "W").Formula = Cells(Zeile1, "W").Formula
Zeile2 = Zeile2 + 2
End If
Next
End With
End Sub
Wo muss ich Deinen Vorschlag einbauen?
MfG Christian

Anzeige
AW: Schon fast! ;-)
22.05.2014 18:06:44
Daniel
na in den letzen IF_Block, bevor du die Zeilennummer hochzählst.
hast du dir den Code auch mal angeschaut und versucht nachzuvollziehen, was da abläuft und was die einzelnen Programmschritte und Befehle ausführen?
Gruß Daniel

läuft....
22.05.2014 18:48:55
Christian
Hallo Daniel,
hab es jetzt zum Laufen bekommen.
Natürlich schau ich mir Codevorschläge an, und versuch auch nachzuvollziehen, was im Code abläuft.
Da ich aber noch wenig Ahnung von der Logik von Vba habe, insbesondere was die Reihenfolge der Anweisungen betrifft, dauert bei mir das verstehen halt manchmal etwas länger!
Der folgende Code läuft, aber für evtl. Verbesserungvorschläge bin ich natürlich jederzeit dankbar!
Private Sub CommandButton20_Click()
'Zusammenstellung
Dim LetzteZeile As Long
Dim Zeile1 As Long
Dim Zeile2 As Long
With wksLvz
LetzteZeile = [Q:W].Find(what:="*", After:=[Q1], LookIn:=xlValues, SearchDirection:=xlPrevious). _
Row
Cells(LetzteZeile + 2, "S").Value = "Zusammenstellung"
Zeile2 = LetzteZeile + 4
Application.ScreenUpdating = False
Range(.Cells(LetzteZeile + 2, "S"), .Cells(LetzteZeile + 2, "W")).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
End With
With Selection
.HorizontalAlignment = xlCenter
End With
For Zeile1 = 26 To LetzteZeile
If Cells(Zeile1, "V").Value Like "Summe*" Then
Cells(Zeile1, "S").Resize(, 5).Copy
Cells(Zeile2, "S").PasteSpecial xlPasteFormats
Cells(Zeile2, "V").Formula = Cells(Zeile1, "V").Formula
Cells(Zeile2, "W").Formula = Cells(Zeile1, "W").Formula
Zeile2 = Zeile2 + 2
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
End Sub

MfG Christian

Anzeige
AW: läuft....
22.05.2014 19:11:08
Daniel
du solltst in dem Codeteil den du selbst geschrieben hast, noch die Selections rausschmeissen.
der Recoder zeichenenet unsere Maus-Optimierte Arbeitsweise auf, in der wir zuerst ein Excelobjekt auswählen (Zellen, Blätter usw) und dann auf diese Auswahl einen Befehl anwenden:
Excelobjekt.Select
Selection.Befehl

in VBA ist das jedoch nicht notwendig, hier kann man den Befehl direkt auf das Excel-Objekt anwenden:
Excelobjekt.Befehl

wenn man mit mehreren Dateien oder Tabellenblättern arbeitet, dann gibt man die immer mit an:
Workbooks("Mappe1.xlsm").Sheets("Tabelle1").Range("A1").Copy

wenn man eine Angabe weglässt, setzt VBA an dieser Stelle das entsprechende Aktive Objekt ein.
desweitern ist die Klammerschreibweise für Zellbezüge ungünstig, weil langsam und nicht tauglich für die Verwendung von Variablen.
hier sollte man entweder RANGE(zelladdresse) oder CELLS(Zeile, Spalten) verwenden, um die Zellen anzusprechen.
gruß Daniel

Anzeige
ohne Select
22.05.2014 20:05:18
Christian
Hallo Daniel,
dieses ohne Select ist noch mein Hauptproblem beim Lernen :-(
Wie schreibt man das genau ohne die Select/Selection Befehle
Meine Versuche mit With Range... End With scheitern immer an den Fehlermeldungen, oder dass dann ganze Zeilen fehlen, die eigentlich hätten mit übertragen werden sollen..
Range(wksLvz.Cells(LetzteZeile + 2, 19), wksLvz.Cells(LetzteZeile + 2, 23)).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 12
End With
With Selection
.HorizontalAlignment = xlCenter
End With
Danke für Aufklärung
MfG Christian

Anzeige
AW: ohne Select
22.05.2014 20:11:18
Hajo_Zi
Hallo Christian,
    With Range(wkslvz.Cells(letzteZeile + 2, 19), wkslvz.Cells(letzteZeile + 2, 23))
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 12
End With
.HorizontalAlignment = xlCenter
End With

AW: ohne Select
22.05.2014 21:02:17
Christian
Hallo Hajo,
bei Deinem Code hat er erst die die Zeile wo Zusammenfassung drin steht leider nicht formatiert, sondern eine Zelle 6 zeilen nach dem Ende.
Aber nach dem ich With.Selection.Font geändert habe ging es!
Danke
MfG Christian

Anzeige
AW: ohne Select
22.05.2014 20:31:00
Daniel
Hi
so:
With Range(wksLvz.Cells(LetzteZeile + 2, 19), wksLvz.Cells(LetzteZeile + 2, 23))
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Font
.Name = "Arial"
.FontStyle = "Fett"
.Size = 12
End With
.HorizontalAlignment = xlCenter
End With
das Excelobjekt, welches du in der Zeile mit WITH beschreibst, wird im folgenden immer dann automatisch eingefügt, wenn ein Objektausdruck mit dem Punkt beginnt.
Beispiel:
With Sheets("Tabelle1")
.Cells(1, 1).Value = "Hallo Welt
With .Cells(1, 1).Font
.Size = 10
.Bold = True
End with
.Cells(2, 1).Value = "xxx"
End With

Wird ausgeführt wie

Sheets("Tabelle1").Cells(1, 1).Value = "Hallo Welt
Sheets("Tabelle1").Cells(1, 1).Font.Size = 10
Sheets("Tabelle1").Cells(1, 1).Font.Bold = True
Sheets("Tabelle1").Cells(2, 1).Value = "xxx"
Gruß Daniel

Anzeige
AW: ohne Select
22.05.2014 21:08:13
Christian
Hallo Daniel,
Danke für die Erklärungen,
funktioniert wie gewünscht.
Mit Hilfe und Erläuterungen von euch Helfern hier im Forum und den m.M. nach sehr guten Erklärungen hier bei den Excel FAQ bei den Services kann man doch Schritt für Schritt langsam aber sicher weiter zum Ziel kommen! ;-)
Nochmals Dank für die Hilfe!
MfG Christian

330 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige