Excel makro
26.04.2007 19:40:01
Clemens
hab mir für die arbeit ein kleines makro gestrickt, das bisher eigentlcih gut funktioniert hat.
im wesentlichen soll es ein csv sheet umformatieren, ausdrucken und als xls speichern.
seit wenigen wochen hab ich aber das problem, dass das makro beim ausführen irrsinnig langsam ist. es scheint so als ob es sich in schleifen verliert...statt 5sec abarbeitung pro file braucht das ganze nun 2 oder mehr minuten..
kann mir jemand sagen, was hier das problem sein könnte? wieso geht der code auf einmal nicht mehr ordentlich?
DANKE im Voraus!
anbei mal der code (bitte um verständniss, ist sicher nicht elegant gelöst,aber hat funktioniert...):
""
Sub abgang()
' abgang Makro
' Makro am 21.7.2006 von cn aufgezeichnet
' Tastenkombination: Strg+a
Rows("1").Select
Selection.Font.Bold = True
If (Range("A2") = "Symbol") Then
Range("A1:M4").Select
Selection.NumberFormat = "#,##0.00"
Rows("2").Select
Selection.Font.Bold = True
Rows("2:4").Select
Selection.Insert Shift:=xlDown
Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:J2"), Type:=xlFillDefault
Rows("2").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A3") = "Symbol") Then
Range("A1:M4").Select
Selection.NumberFormat = "#,##0.00"
Rows("3").Select
Selection.Font.Bold = True
Rows("3:5").Select
Selection.Insert Shift:=xlDown
Range("C3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:J3"), Type:=xlFillDefault
Rows("3").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A4") = "Symbol") Then
Range("A1:M5").Select
Selection.NumberFormat = "#,##0.00"
Rows("4").Select
Selection.Font.Bold = True
Rows("4:6").Select
Selection.Insert Shift:=xlDown
Range("C4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:J4"), Type:=xlFillDefault
Rows("4").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A5") = "Symbol") Then
Range("A1:M6").Select
Selection.NumberFormat = "#,##0.00"
Rows("5").Select
Selection.Font.Bold = True
Rows("5:7").Select
Selection.Insert Shift:=xlDown
Range("C5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:J5"), Type:=xlFillDefault
Rows("5").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A6") = "Symbol") Then
Range("A1:M6").Select
Selection.NumberFormat = "#,##0.00"
Rows("6").Select
Selection.Font.Bold = True
Rows("6:8").Select
Selection.Insert Shift:=xlDown
Range("C6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:J6"), Type:=xlFillDefault
Rows("6").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A7") = "Symbol") Then
Range("A1:M7").Select
Selection.NumberFormat = "#,##0.00"
Rows("7").Select
Selection.Font.Bold = True
Rows("7:9").Select
Selection.Insert Shift:=xlDown
Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Range("C7").Select
Selection.AutoFill Destination:=Range("C7:J7"), Type:=xlFillDefault
Rows("7").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A8") = "Symbol") Then
Range("A1:M7").Select
Selection.NumberFormat = "#,##0.00"
Rows("8").Select
Selection.Font.Bold = True
Rows("8:10").Select
Selection.Insert Shift:=xlDown
Range("C8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:J8"), Type:=xlFillDefault
Rows("8").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
If (Range("A9") = "Symbol") Then
Range("A1:M8").Select
Selection.NumberFormat = "#,##0.00"
Rows("9").Select
Selection.Font.Bold = True
Rows("9:11").Select
Selection.Insert Shift:=xlDown
Range("C9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("C9").Select
Selection.AutoFill Destination:=Range("C9:J9"), Type:= _
xlFillDefault
Rows("9").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
Range("A1:M8").Select
Selection.NumberFormat = "#,##0.00"
Rows("10").Select
Selection.Font.Bold = True
Rows("10:12").Select
Selection.Insert Shift:=xlDown
Range("C10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Range("C10").Select
Selection.AutoFill Destination:=Range("C10:J10"), Type:=xlFillDefault
Rows("10").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
End If
End If
End If
End If
End If
End If
End If
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Seite &P von &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.4921259845)
.FooterMargin = Application.InchesToPoints(0.4921259845)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Seite &P von &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.4921259845)
.FooterMargin = Application.InchesToPoints(0.4921259845)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
Columns("B:B").Select
Selection.ColumnWidth = 8.5
Columns("C:C").ColumnWidth = 8
Columns("H:H").ColumnWidth = 7.3
Columns("K:K").ColumnWidth = 6.86
Columns("N:N").ColumnWidth = 6.14
Columns("I:I").ColumnWidth = 8.14
Columns("G:G").ColumnWidth = 11.5
Columns("E:E").ColumnWidth = 11.5
ActiveWorkbook.SaveAs FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
End With
End Sub
""