optimizing a macro

[The Grinch]

Hi All,

I have the following macro...


Range("b10").Select

Do
ActiveCell.FormulaR1C1 = ActiveCell.Offset(-1, 0) + 1
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(-1, 0).Value = Range("e1").Value

Range("b1").Select

It starts at b10 which is a date, 21 jun. it runs down the colum
putting in the dates 22, 23, 24 June... until it gets to today's date
it stops by checking the cell e1 which contains the formula =today().

this works fine, however as the days go on and on its going to tak
longer and longer to run because it always starts at the beginning, 2
June in b10.

Is there anyway to amend the code to force it to start at the max valu
in column b?

Any help/comments appreciated.

(note: i dont want to use formulae eg =if(b11>today(),"",b10+1) becaus
all cells below todays date have to be empty.

 

[Tom Ogilvy]

Cells(rows.count,2).End(xlup)(2).Select
if activeCell.Row < 10 then Range("B10").Select

Do
ActiveCell.FormulaR1C1 = ActiveCell.Offset(-1, 0) + 1
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(-1, 0).Value >= Range("e1").Value

Range("b1").Select

 

[Daniel.M]

Hi,


' ****************************************************
A few more hints:
Don't Select, just iterate.
Put ScreenUpdating to false until you have finished.
Use a variable to hold the sentinel
Don't use Formula... if you can use the .Value instead.

Sub Opt2()

Dim i as Long, k As Double
Application.ScreenUpdating = False
k = Range("E1").Value ' or k = Date

With ActiveSheet.Range("B10")
.Select
Do
i = i + 1
.Cells(i, 1) = .Cells(i - 1, 1) + 1
Loop Until .Cells(i, 1).Value = k
End With

Range("B1").Select
Application.ScreenUpdating = True

End Sub

' ****************************************************
' When the values to write follow a pattern, you can use a formula
' Using formula
Sub Opt3()

Dim i&
Application.ScreenUpdating = False

i = Range("E1").Value - Range("B9")
With ActiveSheet.Range("B10").Resize(i, 1)
.FormulaR1C1 = "=R[-1]C+1"
.Value = .Value
End With

Range("B1").Select
Application.ScreenUpdating = True

End Sub

' ****************************************************
' Sometimes Excel has its own commands very close to what you need
' Using Fill

Sub Opt4()
Range("B9").Resize(10000).DataSeries Rowcol:=xlColumns, _
Type:=xlChronological, Date:= _
xlDay, Step:=1, Stop:=Range("E1") * 1, Trend:=False
End Sub


Regards,

Daniel M.

 

[The Grinch]

Thanks for all the help guys! i'm now sorted. cheers