Simple Do Loop!
 

Hi

I have a list of dates in D that will be varying in size. In G I want to
add the formula =TODAY()-D2, etc.

I want this to appear as the result of a certain macro and deleted if
another macro is run, as something else will go here. This part I can manage.

I'm struggling with making the Do Loop only continue if there is something
in D.

Any help, much appreciated!

Adam

Simple Do Loop!
 

Adam,

Try this

Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Range("G2:G" & Lastrow).Formula = "=TODAY()-D2"

Mike

"Adam" wrote:

Hi

I have a list of dates in D that will be varying in size. In G I want to
add the formula =TODAY()-D2, etc.

I want this to appear as the result of a certain macro and deleted if
another macro is run, as something else will go here. This part I can manage.

I'm struggling with making the Do Loop only continue if there is something
in D.

Any help, much appreciated!

Adam

Simple Do Loop!
 

Spot on

Thanks Mike

Adam


"Mike H" wrote:

Adam,

Try this

Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Range("G2:G" & Lastrow).Formula = "=TODAY()-D2"

Mike

"Adam" wrote:

Hi

I have a list of dates in D that will be varying in size. In G I want to
add the formula =TODAY()-D2, etc.

I want this to appear as the result of a certain macro and deleted if
another macro is run, as something else will go here. This part I can manage.

I'm struggling with making the Do Loop only continue if there is something
in D.

Any help, much appreciated!

Adam

Simple Do Loop!
 

Try code like the following

Dim R As Range
Set R = Range("D1") ' or starting cell
Do Until R.Value = vbNullString
' do something with R
Set R = R(2, 1) ' move R down one row
Loop

This will start at D1 or whatever cell you specify in the Range
object, and move down one row at a time until an empty cell is
encountered. Replace the " ' do something with R " with your actual
code. Note that in this sort of loop, the ActiveCell is never changed,
so if the ActiveCell pointed to D1 before to loop executed, it will
still point to D1 even after the loop has set R down to D100. The
ActiveCell never changes unless you write code to explicitly change
it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 28 Sep 2009 03:47:01 -0700, Adam
wrote:

Hi

I have a list of dates in D that will be varying in size. In G I want to
add the formula =TODAY()-D2, etc.

I want this to appear as the result of a certain macro and deleted if
another macro is run, as something else will go here. This part I can manage.

I'm struggling with making the Do Loop only continue if there is something
in D.

Any help, much appreciated!

Adam