Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1020to1024
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

Bereich im Code variabel gestalten

Bereich im Code variabel gestalten
06.11.2008 12:11:41
Lanima
Hallo zusammen,
ich habe folgenden Code per Recorder aufgezeichnet:

Sub Rechnung()
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Range("K60").Select
ActiveCell.FormulaR1C1 = "=SUM(DATA11)"
Range("L59").Select
Selection.AutoFill Destination:=Range("L59:L60"), Type:=xlFillDefault
Range("L59:L60").Select
Range("M60").Select
ActiveCell.FormulaR1C1 = "=SUM(DATA13)"
Range("N59").Select
Selection.AutoFill Destination:=Range("N59:N60"), Type:=xlFillDefault
Range("N59:N60").Select
Range("K60:N60").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("N59").Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub


Da die Anzahl Zeilen aber variiert (die Spalten sind immer gleich), benötige ich das ganze variabel.
Also Summen usw. unter die letze gefüllte Zeile und dann alles einrahmen.
Ich hoffe, es ist einigermaßen verständlich.
Kann mir jemand helfen?
Gruß
Martin

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bereich im Code variabel gestalten
06.11.2008 12:38:52
Reinhard
Hi Martin,
was verbirgt sich hinter Data11,Data13? Ein dynamischer oder fester Spaltenbereich? Welche Zellen umfaßt der?
Bringen diese beiden Codes das gleiche Ergebnis?

Sub nn()
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Range("K60").Select
ActiveCell.FormulaR1C1 = "=SUM(DATA11)"
Range("L59").Select
Selection.AutoFill Destination:=Range("L59:L60"), Type:=xlFillDefault
Range("L59:L60").Select
Range("M60").Select
ActiveCell.FormulaR1C1 = "=SUM(DATA13)"
Range("N59").Select
Selection.AutoFill Destination:=Range("N59:N60"), Type:=xlFillDefault
End Sub
Sub nn2()
Cells.Interior.ColorIndex = xlNone
Cells.Font.ColorIndex = 0
Range("K60").Formula = "=SUM(DATA11)"
Range("L60") = Range("L59")
Range("M60").Formula = "=SUM(DATA13)"
Range("N60") = Range("N59")
End Sub


Gruß
Reinhard

Anzeige
AW: Bereich im Code variabel gestalten
06.11.2008 12:53:00
Lanima
Hallo Reinhard,
DATA11 und DATA 13 wurden so aufgezeichnet, es ist jeweils der Bereich Zeile 2 bis Zeile 59 der Spalten K und M, die Spalten sind immer die gleichen.
Beim nächsten Mal können es aber auch mal zb. 75 oder nur 40 Zeilen sein, deshalb brauche ich das dynamisch.
Die beiden Codes erzielen das gleich Ergebnis!
Gruß
Martin
AW: Bereich im Code variabel gestalten
06.11.2008 13:14:26
Rudi
Hallo,
könnte passen, ist aber optimierungsbedürftig:

Sub tt()
Cells.Interior.ColorIndex = xlNone
Cells.Font.ColorIndex = 0
With Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Resize(, 4)
.FormulaR1C1 = "=Sum(R2C;R[-1]C)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 14)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub


Gruß
Rudi

Anzeige
AW: Bereich im Code variabel gestalten
06.11.2008 13:22:00
Lanima
Hallo Rudi,
der Code bringt"Laufzeitfehler 1004, Anwendungs- oder objektdefinierter Fehler",
Markiert mir aber keine fehlerhafte Codezeile.
Wo dran kann das liegen?
Gruß
Martin
AW: Bereich im Code variabel gestalten
06.11.2008 13:26:04
Rudi
Hallo,
Tippfehler.

Sub tt()
Cells.Interior.ColorIndex = xlNone
Cells.Font.ColorIndex = 0
With Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Resize(, 4)
.FormulaR1C1 = "=Sum(R2C:R[-1]C)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 14)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub


Gruß
Rudi

Anzeige
Fast perfekt
06.11.2008 13:42:13
Lanima
Hallo Rudi,
das funzt wunderbar aber, (es gibt ja immer ein aber ;-)),
er bildet in den Spalten L & N auch eine Summe, dort stehen aber die Maßeinheiten.
Diese beiden Codezeilen müßten noch dynamisch werden:
Selection.AutoFill Destination:=Range("L59:L60"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("N59:N60"), Type:=xlFillDefault
glaube ich zumindestens.
Gruß
Martin
AW: Fast perfekt
06.11.2008 19:52:00
Rudi
Hallo,
Option Explicit

Sub tt()
Cells.Interior.ColorIndex = xlNone
Cells.Font.ColorIndex = 0
With Cells(Rows.Count, 11).End(xlUp)
.Offset(1, 0).FormulaR1C1 = "=Sum(R2C:R[-1]C)"
.Offset(1, 1) = .Offset(0, 1)
.Offset(1, 2).FormulaR1C1 = "=Sum(R2C:R[-1]C)"
.Offset(1, 3) = .Offset(0, 3)
With .Offset(1, 0).Resize(, 4)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End With
With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 14)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub


Gruß
Rudi

Anzeige
AW: Fast perfekt
07.11.2008 12:45:00
Lanima
Hallo Rudi,
entschuldige die späte Antwort,ich bin erst jetzt dazu gekommen.
Das ist perfekt.
Ich danke Dir vielmals
Gruß
Martin
AW: Bereich im Code variabel gestalten
06.11.2008 13:23:06
Reinhard
Hallo Rudi, Martin,
vielleicht wie nachfolgend.
Aber wenn die Tabelle "schrumpft" müßten ja alle "alten" rahmen weg, da fand ich nix schnelles wie:
Cells.AllBorders=xlNone oder so, deshalb Frage noch offen.

Sub tt()
Cells.Interior.ColorIndex = xlNone
Cells.Font.ColorIndex = 0
With Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Resize(, 4)
.FormulaR1C1 = "=Sum(R2C;R[-1]C)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
For N = 7 To 12
With .Borders(N)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next N
End With
With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 14)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
For N = 7 To 12
With .Borders(N)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next N
End With
End Sub


Gruß
Reinhard

Anzeige
AW: Bereich im Code variabel gestalten
06.11.2008 13:28:02
Lanima
Hallo Reinhard,
ich habe mich unverständlich ausgedrückt. Tschuldigung.
Es ist nicht immer die gleiche Tabelle, die Daten werden jedes Mal neu aus SAP ausgelesen und dann
von mir aufbereitet.
Gruß
Martin
AW: Bereich im Code variabel gestalten
06.11.2008 13:28:00
Rudi
Hallo Reinhard,

da fand ich nix schnelles 


Cells.Borders.LineStyle = xlNone
Gruß
Rudi

Danke dir Rudi o.w.T.
08.11.2008 08:53:00
Reinhard

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige