Zeilenumbruch in VBA

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Zeilenumbruch in VBA
von: Thomas
Geschrieben am: 27.10.2015 12:33:52

Hallo excelfreunde,
ich versuche gerade dem macro unten beizubringen das der zeilenumbruch (Seitenwechsel) automatisch gesetzt wird. Jedoch soll der Zeilenbereich ( jedes Blatt) immer mit dem teilwort "ergebnis" in spalte b enden. So möchte ich erreichen das immer ein in sich abgeschlossenes teil ergebnis auf jedem Batt steht.
Sozusagen passt die ergebniszeile noch mit auf das Blatt sonst nimm ein neues.
Im Netz finde ich leider dazu nichts.
Hatt jemand eine Idee oder ein link für mich?
liebe grüsse thomas

Private Sub CommandButton30_Click()
Dim AnzahlEinträgeZeilen As Integer
Dim AnzahlEinträgeSpalten As Integer
Dim anzseiten As Integer
AnzahlEinträgeZeilen = WorksheetFunction.CountA(Sheets("Auswertung Allgemein").Range("A:A"))
AnzahlEinträgeSpalten = WorksheetFunction.CountA(Sheets("Auswertung Allgemein").Range("1:1"))
With ActiveSheet.PageSetup
Dim i
''''''''' Druckbereich setzen von hier''''''''''''''''''''''''''''''''''''''#
'####################################################################################
Dim lngUeberschriftRow As Long
Dim lngNameCol As Long
Dim strQuellueberschrift As String, strTabelle As String
Dim strSpesen As String
strQuellueberschrift = "name"
strSpesen = "wert"
strTabelle = "Auswertung Allgemein"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _
''''
'Zeile und Spalte suchen in der n Fahrer ist
 With Sheets(strTabelle)
    lngUeberschriftRow = .Cells.Find(strQuellueberschrift, LookAt:=xlWhole).Row
    lngNameCol = .Rows(lngUeberschriftRow).Find(strQuellueberschrift, LookAt:=xlWhole).Column
 End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _
'''''''
Dim rng As Excel.Range
    ' mit Name suchen`' Set rng = ThisWorkbook.Sheets(strTabelle).Cells(lngUeberschriftRow,  _
lngNameCol).CurrentRegion
    'MsgBox rng.Address
Set rng = ThisWorkbook.Sheets(strTabelle).Cells(10, 2).CurrentRegion
'MsgBox rng.Address
 With ActiveSheet.PageSetup
        .PrintArea = rng.Address
        .PrintTitleRows = "$10:$10"
        .PrintTitleColumns = "$B:$B"
    End With
'Worksheets("Auswertung Allgemein").PageSetup.PrintArea = Range(Range("A1").Text).Address
'  druckbereich setzen bis hier
'############################################################################################### _
###
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Seitenbreite definieren
.FitToPagesWide = 1
'Seitenhöhe definieren
.FitToPagesTall = False
'Seitenanzahl bestimmen
anzseiten = ExecuteExcel4Macro("Get.Document(50)")
'Rechte Fußnote mit Seitenanzahl belegen
.RightFooter = anzseiten
'Festlegung auf Querformat
.Orientation = xlLandscape                                               ' xlPortrait   _
Hochformat
If MsgBox(" Bitte endscheide drückst Du  Ja dann wird Querformat gedruckt , Wenn nein drückst  _
wird Hochformat gedruckt in jedem Fall kommt erst die Seitenansicht", vbYesNo) = vbYes Then
.Orientation = xlLandscape
Else
.Orientation = xlPortrait
End If
End With
Worksheets(strTabelle).PrintPreview
End Sub

Bild

Betrifft: AW: Zeilenumbruch in VBA
von: Thomas
Geschrieben am: 27.10.2015 14:47:48
Hallo excelfreunde,
mit dem untenstehenden Macro kann man gezielt Seitenumbrüche setzen.
Könnte dies ein anfang zur Lösung sein?
liebe grüsse thomas

Sub Seitenumbruch()
  Dim L As Long
  ActiveSheet.ResetAllPageBreaks   ' seitenumbrüche entfernen
  Application.ScreenUpdating = False
  For L = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(L, 1).Value Like "*XYZ*" Then
'    Cells(L, 1).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(L, 1).Offset(1, 0)
    End If
  Next
  Application.ScreenUpdating = True
End Sub


Bild

Betrifft: AW: Zeilenumbruch in VBA
von: Esmo
Geschrieben am: 27.10.2015 17:31:33
Moin Thomas,
Cells ( Zeile, Spalte) heißt als Spalte "B" muß es heißen Cells(Zeile, 2). Und wir brauchen die Zelle für das Setzen des Umbruchs eigentlich nicht erst selektieren:

Sub Seitenumbruch()
  Dim L As Long
  Application.ScreenUpdating = False
  ActiveSheet.ResetAllPageBreaks   ' seitenumbrüche entfernen
  For L = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(L, 2).Value Like "*ergebnis*" Then
      ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(L, 2).Offset(1, 0)
    End If
  Next
  Application.ScreenUpdating = True
End Sub
Das Application.ScreenUpdating macht das ganze nur schneller, indem Darstellung des Arbeitsblatts nicht aktualisiert wird - bis Du es wieder "scharf" schaltest mit =True.
Gruß
Ralph

Bild

Betrifft: AW: Zeilenumbruch in VBA
von: Thomas
Geschrieben am: 27.10.2015 18:36:15
Hallo Ralph,
besten dank für die hilfe dies sieht schon mal besser aus als meins.
Mit diesem Macro könnte man für jedes *Ergebnis* in der Zeile eine extra Seite festlegen.
Aber da es manchmal nur drei Zeilen sind wäre dies viel Papier was ich produzieren würde.
Ich könnte mit
Name: Seite, Bezug =Datei.zuordnen(64+0*Jetzt())
A1: 1
In A2:Axx: =WENN(ISTZAHL(VERGLEICH(ZEILE();Seite;));VERGLEICH(ZEILE();Seite;)+A$1;"")
eine Seitenzahl setzen. könnte man dann weiterkommen?
Mein Ziel ist es das am Blattende immmer ein *Ergebnis* in spalte B steht.
Ich grüble und grüble aber noch habe ich einfach nicht die Logik erkannt wie man das Problem lösen könnte.
Ich habe schon herausbekommen das man möglicherweise was mit
ExecuteExcel4Macro("Get.Document(50) oder ExecuteExcel4Macro("Get.Document(64) machen könnte.
Aber weiter komme ich einfach nicht.
Liebe grüsse thomas

Bild

Betrifft: AW: Zeilenumbruch in VBA
von: firmus
Geschrieben am: 27.10.2015 19:49:57
Hallo Thomas,
ich glaube, dass hier ein ganz anderer Ansatz nötig ist.
Du willst nicht nach jedem "Ergebnis" einen Seitenumbruch, sondern:
Annahme:
Du hast Blöcke von Zeilen bei denen jeweils die letzte Zeile des Blocks "Ergebnis" enthält.
Beim Ausdrucken willst Du NICHT, dass ein Block zerrissen wird. D.h., dass "Ergebnis" auf einer anderen Seite steht als der vorgegangene Teil des Blockes.
Annahme richtig?
Dann würde ich die Seite ausmessen in Pixel oder Zentimeter und die Zeilenhöhen addieren.
Damit kannst Du unabhängig von der Blocklänge die Seitenumbrüche steuern. Bei Blöcken, die länger
als eine Seite werden musst Du Dir noch ein Vorgehensweise überlegen.
Hilfsmittel dazu:
ActiveSheet.Cells(1, 1).Height
bringt die Höhe der Zelle A1, und damit der Zeile 1.
http://www.pcwelt.de/ratgeber/Zentimetergenau-einstellen-Ratgeber-Excel-426058.html
erläutert die Zusammenhänge der Maßzahlen bzgl. Excel.

Lass wissen, ob Dich der Ansatz weiterbringt.
Gruß
Firmus

Bild

Betrifft: möglicherweise neuer Ansatz?
von: Thomas
Geschrieben am: 27.10.2015 20:08:32
hallo Firmus,
ja so wie du das Problem darstellst ist es richtig angenommen. So zu beschreiben ist es besser und bringt neue Ideen.
Ich werde deine Idee energisch nachgehen und ausprobieren aber da muss ich erstmal lesen und rumprobieren.
Hab recht vielen dank für diesen neuen Ansatz.
Sei aber bitte nicht sauer das ich den beitrag trotz deinem Ansatz offen lasse.
Versprochen sobald es gelöst ist schliesse ich Ihn und gebe dir bescheid.
liebe grüsse thomas

Bild

Betrifft: AW: möglicherweise neuer Ansatz?
von: Esmo
Geschrieben am: 28.10.2015 01:31:40
Moin!
Den Ansatz von Firmus hätte ich jetzt mit Anzahl Zeilen gemacht. Dann mußt Du Dir allerdings merken, wo der letzte Zeilenumbruch war und dann schauen, ob der nächste Ergebnisblock noch auf die Seite paßt. Wenn nicht, müsstest Du vorher einen einfügen. Falls der Block eh zu lang ist, mußt Du noch definieren, was passieren soll.
Gruß
Ralph

Bild

Betrifft: AW: möglicherweise neuer Ansatz?
von: Thomas
Geschrieben am: 28.10.2015 04:49:05
Hallo Ralph,
cool das Du dich damit beschäftigst.
Der Ansatz mit den Zeilen zählen könnte vielleicht auch der weg sein. Wenn man die Zeilenhöhe vom Blatt auf 20 setzt sind es je Seite immer 36 Zeilen als konstante. Leide habe ich jetzt keine Zeit mehr ich mache heut abend weiter. Dann werde ich mal sehen wie weit ich mit formeln komme
Wenn ich erstmal die tatsache aus den Augen nehme das es um ein Seitenumbruch geht. Mit der 36 müsste es irgendwie gehen jedes *ereignis* eine "virtuelle" seitenzahl oder Merkmal) zu verpassen.
Dann könnte man mit dem macro dieses zeichen suchen und den Umbruch setzen. Das wird noch spannend.
liebe grüsse thomas

Bild

Betrifft: Zeilen zählen
von: Thomas
Geschrieben am: 28.10.2015 05:54:38
Hallo excelfreunde,
kann jemand eine formel schreiben die die anzahl der zeilen zwischen zwei bestimmten texten (teilergebnis) zählt und ist die zahl kleiner als 36 dann zähle weiter bis zum nächsten gleichen text bis die zahl 36 ereicht ist, dann setze eine 36 daneben und fange an dieser stelle von vorn an bis keine 36 mehr zufinden ist?
liebe grüsse thomas

Bild

Betrifft: AW: Zeilen zählen
von: firmus
Geschrieben am: 28.10.2015 16:57:41
Hi Thomas,
ich habe mir das nochmals ansehen, muss doch einfach zu machen sein, und es ist es auch.
Es wird nur die Seitenlänge in einer Maßeinheit[ME] benötigt. Es muss danach immer diese ME benutzt werden. Die Anzahl der Zeilen pro Seite hilft diese Seitenlänge zu ermitteln. Was diese ME darstellt ist vernachlässigbar.
1. Addiere alle Höhenangaben der Zeilen der ersten Seite: Das ergibt die zugrunde gelegte Seitenlänge.
Während Du die Liste per VBA von oben nach unten durchgehst:
2. Addiere alle Höhenangaben der Zeilen pro Block,
3. Summiere diese Blockhöhen je Block solange bis diese B.Summe größer als die ermittelte Seitenlänge ist.
4. Wenn das passiert, setze einen Block zurück und füge einen Seitenumbruch ein.
5. Sollte ein Block länger als eine Seite sein, dann muss das noch markiert werden.
Schau Dir mal das Beispiel an, es sollte in etwa das tun was Du suchst.
https://www.herber.de/bbs/user/101102.xlsm
Gruß
Firmus

Bild

Betrifft: AW: Zeilen zählen
von: Thomas
Geschrieben am: 28.10.2015 17:48:28
Hallo Firmus,
oh man
das sieht ja richtig lecker aus ich bin noch auf arbeit ich melde mich heute noch mal,
sowie ich mir das richtig angeschaut hab.
vorab aber schon mal vielen vielen dank das du soviel interesse an mein problem zeigst und Dir soviel arbeit machst.
liebe lieb grüsse thomas

Bild

Betrifft: AW: Zeilen zählen -
von: Thomas
Geschrieben am: 28.10.2015 22:56:46
Hallo Firmus,
Das ist eine super sache was Du da gebaut hast. Durch deine Idee und Umsetzung kann man sich sogar aussuchen ob man Hoch oder quer druckt.
Ich konnte noch nicht bis zum ende testen das zusammenspiel mit den anderen macros. Aber wie es aussieht funktioniert es schon richtig gut.
An dieser stelle
Cells(BlockEndV, 2).Interior.ColorIndex = 4 'gruen, absichtlicher Seitenwechsel
kommt hin und wieder ein Fehler " Anwendungs oder objektorientierter fehler " ich weiss noch nicht genau wann aber wahrscheinlich wenn man zu wenige Zeilen in der ersten maske angibt. Kann man dies noch irgendwie abfangen? Wenn Du noch eine Idee hast den zwangsumbruch zu vermeiden wäre es auch noch klasse. Hier vielleicht so wenn das macro es machen muss dann Macro stopp und eine Meldung wie " bitte geben Sie weniger Zeilen an".
Aber wie schon geschrieben super super klasse Sache was ich noch vorschlage ist eigentlich nur kosmetik. Ich freue mich riesig über deine Lösung. Ich hatte noch stundenlang mit den zeilenzahlen gespielt. Aber dein lösungsansatz und umsetzung sind viel besser.
Hab recht vielen dank dafür nochmal.
liebe liebe grüsse thomas

Bild

Betrifft: AW: Zeilen zählen -
von: firmus
Geschrieben am: 28.10.2015 23:10:31
Hallo Thomas,
"zwangsumbruch vermeiden"
muss ich mal ansehen, aber wenn eine Gruppe länger als eine Seite ist, muss eine Entscheidung getroffen werden. Vllt. hast Du eine Idee für eine Entscheidung, vllt. gesamte Arbeit zurückweisen bis diese Stellen entsprechend korrigiert sind, sprich Nachbesserung der Gruppen.
Um das zu ermöglichen ist auch die Einfärbung gewählt.
"gelegentlicher Fehler bei .....Farbe setzen.
Es kann sein, dass es auftritt wenn gleich die erste Gruppe zu groß ist.
Wäre prima wenn Du hier konkretere Hinweise hättest, das macht die Suche schneller.
Abfangen kann man das vermutlich über "on Error resume", das ist aber "quick and dirty".
Eine Anpassung der Logik wäre schöner.
Motivation: Es macht Spaß zu sehen, dass man das drauf hat, und wenn man damit helfen kann - gerne.
Außerdem: Beschäftigung für die grauen Zellen hält diese fit.
Gruß
Firmus

Bild

Betrifft: AW: Zeilen zählen -
von: Thomas
Geschrieben am: 29.10.2015 05:54:16
Hallo Firmus,
hab vielen dank für deine Antwort.
Das der stopp den ich vorschlug nicht gut ist ist mir dann auch aufgefallen. Dann steht man wie Kuh vorm Dorf. Muss jetzt leider los und habe erst heut abend wieder zeit zum testen und so.
dann meld ich mich noch mal.
liebe grüsse thomas
hab vielen dank für deine

Bild

Betrifft: AW: Zeilen zählen -
von: firmus
Geschrieben am: 29.10.2015 08:33:26
Hi Thomas,
es war tatsächlich "der erste Block zu lang" die Ursache für den Abbruch.
Hier die korrigierte Version.
https://www.herber.de/bbs/user/101114.xlsm
Gruß
Firmus

Bild

Betrifft: AW: Zeilen zählen -
von: Thomas
Geschrieben am: 29.10.2015 19:31:39
Hallo Firmus,
ich konnte jetzt viel testen, das Macro laüft jetzt stabil.
Aber ich wollte mal fragen ob Du dem Macro noch so etwas wie siehe unten beibringen kannst.
Damit würde ich flexibler werden. Sozusagen Es soll sich erst den Druckbereich ( die Zeile )
suchen ab der es starten soll.
Zur Zeit beginnt die Berechnung immer in Zelle A1. Gern würde ich diese Macro auch noch in einer anderen tabelle einbauen. Hier ist es aber möglich das die zu druckenden Daten sich in den Bereich B10 bis "CurrentRegion" befinden.
Ich bekomme es irgendwie nicht gebacken.
Kannst Du noch mal schauen?
Wenn man in der Abfrage wie viel Zeilen? auf abrechen drückt geht es in den Debugger Modus kann man hier sagen ok dann beende alles? Meine varianten die ich kenne habe ich alle getestet leider ohne Erfolg.
Nun ja mein VBA ist enfach noch zu wenig.
Die wären aber wirklich die einzigen Sachen was mir noch auffiehl der rest ist einfach nur spitze
von der Idee bis zur Umsetzung.
auch wennich es schon oft gesagt hab hab recht vielen dank dafür
liebe grüsse thomas

strQuellueberschrift = "Kunde"
With Sheets(strTabelle)
lngUeberschriftRow = .Cells.Find(strQuellueberschrift, LookAt:=xlWhole).Row
lngNameCol = .Rows(lngUeberschriftRow).Find(strQuellueberschrift, LookAt:=xlWhole).Column
Dim rng As Excel.Range
Set rng = ThisWorkbook.Sheets(strTabelle).Cells(lngUeberschriftRow_ lngNameCol).CurrentRegion
.................
End With

Bild

Betrifft: AW: Zeilen zählen - wie?
von: firmus
Geschrieben am: 31.10.2015 13:54:50
Hi Thomas,
Teil 1:
hier die korrigierte Version (Abbrechen endet jetzt sauber).
https://www.herber.de/bbs/user/101158.xlsm
Teil 2:
Die Angabe der letzten Zeile zum Bestimmen der Seitenlänge muss erhalten bleiben.
Danach lässt sich ein Dialog einrichten um den Bereich zu bestimmen.
Ich sehe im Moment 3 Möglichkeiten einen solchen Teilbereich auszuwählen:
a) Markieren aller Zeilen in einer Spalte (z.B. "A") um die erste und letzte Zeile zu erkennen.
Das wird sich bei langen Listen vermutlich schwierig handhaben lassen.
Danach Aufruf des Makros.
b) Dialog für erste zu druckende Zeile, und Dialog für die letzte zu druckende Zeile.
Danach Aufruf des Makros.
c) Automatisches Erkennen eines Begriffes je für Anfang und Ende.
z.B. in eine Zeile "fang hier an" und weiter unten in eine Zeile "hör hier auf".
Danach Aufruf des Makros.
Bleibt die Frage, ob, wenn ab einer bestimmten Zeile gedruckt werden soll, dann aber alles ab hier.
Oder ob tatsächlich zum markierten Start auch ein Ende innerhalb der Datei markiert werden soll.
Gruß
Firmus
PS: ich bin nächste Woche unterwegs, eventuell mit wenig XLS-zugang.
Falls dieser Ablauf im Archiv verschwindet und noch nicht komplett ist, einfach neuen starten, möglichst gleicher Titel oder auch meinen Namen als Teil des Titels, ähnliches sieht man gelegentlich.

Bild

Betrifft: AW: Zeilen zählen - wie?
von: Thomas
Geschrieben am: 31.10.2015 16:04:28
Hallo Firmus,
hab recht vielen dank für deine Hilfe das ist total lieb von Dir.
Dein Vorschlag c wäre für mich der interessanteste für mich deshalb
habe ich unten meine variante wie ich den zu druckenden bereich definiere.
Würde dies weiterhelfen? Oder soll ich noch was anderes bauen?
Besten dank auch für den hinweis das der Beitrag nicht mehr zu erreichen sein wird. ich werde dann
Montag ein neuen eröffnen. Mit dem gleichen Namen.
liebe grüsse thomas

Sub benutzter_bereich()
Dim lngI As Long
Dim zeilenbereich As String
Dim spaltenbereich As String
Dim erstespalte As Long
Dim letztespalte As Long, letztespalte2 As Long
Dim letztezeile2 As Long
Dim benutzterbereich As Variant
Dim vErgebnis As String
Dim AnzahlEinträgeZeilen As Integer
Dim benutzterbereich2 As Variant
Dim AnzahlEinträgeSpalten1 As Integer
Dim AnzahlEinträgeZeilen2 As Integer
Dim AnzahlEinträgeSpalten2 As Integer
Dim AnzahlEinträgeZeilen3 As Integer
Dim AnzahlEinträgeSpalten3 As Integer
Dim ersteZeile As Long, ersteZeile2 As Long
Dim letztezeile As Long
Dim Quellueberschrift As String, strSpesen As String, strTabelle As String
Dim vntList() As Variant
'####################################################################
Quellueberschrift = "name"
strSpesen = "spesen"
vErgebnis = "*ergebnis*"                      ' im macro wird worteil ergebnis gesucht zum  _
formatieren wichti
strTabelle = "Auswertung Allgemein"
With Sheets(strTabelle)
  ersteZeile = .Cells.Find(Quellueberschrift, LookAt:=xlWhole).Row
  erstespalte = .Rows(ersteZeile).Find(Quellueberschrift, LookAt:=xlWhole).Column
  letztespalte = .Rows(ersteZeile).Find(strSpesen, LookAt:=xlWhole).Column
  ersteZeile2 = .Cells(ersteZeile, erstespalte).Row
  letztespalte2 = Sheets(strTabelle).Cells(Rows.Count, erstespalte).End(xlUp).Row
  letztezeile = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
  letztezeile2 = Sheets(strTabelle).Cells(Sheets(strTabelle).Rows.Count, 1).End(xlUp).Row
  
  benutzterbereich2 = Range(Cells(ersteZeile, erstespalte), Cells(letztezeile, letztespalte)). _
Address
 MsgBox benutzterbereich2
  
  
  End With
  End Sub


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zeilenumbruch in VBA"