2

i want to make a macro that checks if the Date in Column A ist less then today. If so the macro should insert "done" in Column G.

Sub DoneCheck()

Dim rngZelle As Range, strText As String
With ActiveSheet

For Each c In Sheets("3. Umlagerungen").Range("A:A")
If c.Value < Int(Now) And Not (IsEmpty(c.Value)) Then c.Offset(0, 6).Value = "done"

Next c
End Sub

I know that it is very poorly coded but it works... The reason why I'm looking for help is because it runs really slow...

I tried it with Application.ScreenUpdating but it doesn't change anything.

Thank you in advance & greetings

Ralph
  • 9,284
  • 4
  • 32
  • 42
Bluesector
  • 329
  • 2
  • 11
  • 21
  • Check [this thread for some optimization tips](http://stackoverflow.com/questions/20738373/can-i-make-this-macro-more-efficient-or-faster/20754562#20754562), there might be more such threads. Did you check and implement them? – Techie May 03 '16 at 10:20

1 Answers1

3

You should only check the used cells in column A, and adding Application.ScreenUpdating = False always helps speed things up: 

Sub DoneCheck()
Dim eRow as long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

eRow = Thisworkbook.Sheets("3. Umlagerungen").Cells(Rows.Count,1).End(xlUp).Row

For Each c In Thisworkbook.Sheets("3. Umlagerungen").Range("A2:A" & eRow)
    If c.Value < Int(Now) And Not (IsEmpty(c.Value)) Then c.Offset(0, 6).Value = "done"
Next c

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Jordan
  • 4,424
  • 2
  • 18
  • 32
  • It seems as if it has become a little faster, but it is still very slow. The amount of data behind it, is not particularly large. – Bluesector May 03 '16 at 10:22
  • Are there any calculations in the sheet? It might help turning those off as well while the macro runs and then turning them back to automatic when the loop finishes e.g. `Application.Calculation = xlCalculationManual` at the beginning and `Application.Calculation = xlCalculationAutomatic` at the end – Jordan May 03 '16 at 10:25