Code schneller machen
16.05.2013 13:56:50
Anfängerin13
ich habe jetzt einen Code, der durch fast 8790 Zeilen läuft, und daher etwas lange (über 40 min) braucht.
Kann man den vielleicht etwas schlauer schreiben und dadurch schneller machen?
Vielen Dank an euch schon mal.
Sub Optimize()
Dim noCells As Integer
Dim highDemandRow As Integer
Dim sumCheck As Integer
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlAutomatic
.MaxChange = 0.001
.StatusBar = True
End With
Worksheets("DATA").Select
noCells = WorksheetFunction.CountA(Range(Cells(3, 68), Cells(3, 68).End(xlDown)))
Range(Cells(3, 9), Cells(noCells + 2, 9)).ClearContents
Range(Cells(3, 11), Cells(noCells + 2, 11)).ClearContents
For i = 1 To noCells
sumCheck = WorksheetFunction.Sum(Range("BQ:BQ"))
Columns("BP:BP").Select
Columns("BP:BP").Find(What:=i, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Select
On Error Resume Next
highDemandRow = Selection.row
Application.StatusBar = "Completed " & i & " of " & noCells & " hours"
If (sumCheck = 0) Then
If (Cells(highDemandRow, 10) > 0) Then
If Cells(highDemandRow, 10) >= Cells(highDemandRow, 8) Then
Cells(highDemandRow, 9).Value = Cells(highDemandRow, 8)
Cells(highDemandRow, 11).Value = Cells(highDemandRow, 8)
Else
Cells(highDemandRow, 9).Value = Cells(highDemandRow, 10)
Cells(highDemandRow, 11).Value = Cells(highDemandRow, 10)
End If
End If
Else
Columns("BP:BP").Find(What:=(i - 1), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Select
highDemandRow = Selection.row
Range(Cells(highDemandRow, 9), Cells(highDemandRow, 9)).ClearContents
Range(Cells(highDemandRow, 11), Cells(highDemandRow, 11)).ClearContents
End If
Next i
Application.StatusBar = ""
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.StatusBar = False
End With
End Sub