Here is the excel ws and the code follows.

1/1/2005 start date 1/1/2005
end date 2/2/2006

This code is supposed to copy the start date, then extract the next trading
days date from DVS referencing the start date through the formula, and do so
until the trading day date = end date. When it's more than that it should
erase the date. So the result should be a list of trading days between and
including the start and end dates.

However, my code only puts a counter from 2 to 20 - how to you make it from
2 to infinity though? Then when i execute this code, it only copies the start
date and doesn't do anything beyond that. Could you guys help me pls?

Thanks.

Sub click()

Dim counter As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate

For counter = 2 To 20
curCell = Worksheets("sheet3").Cells(counter, 3).FormulaR1C1 =
"=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(counter, 3) = ""
Next counter

End Sub

wait i made it work for this range for this range with the following code, my
question is how do i make the code maybe more efficient and not limited from
2 to 20 but from 2 to infinity?

Sub click()

Dim counter As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate

For counter = 2 To 20
curCell = Worksheets("sheet3").Cells(counter, 3).Select
ActiveCell.FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
If curCell endDate Then Cells(counter, 3) = ""
Next counter

End Sub

worksheet effect

1/1/2005 start date 1/1/2005
1/3/2005 end date 2/2/2006
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/10/2005
1/11/2005
1/12/2005
1/13/2005
1/14/2005
1/17/2005
1/18/2005
1/19/2005
1/20/2005
1/21/2005
1/24/2005
1/25/2005
1/26/2005
1/27/2005


"alexandraVBAgirl" wrote:

Here is the excel ws and the code follows.

1/1/2005 start date 1/1/2005
end date 2/2/2006

This code is supposed to copy the start date, then extract the next trading
days date from DVS referencing the start date through the formula, and do so
until the trading day date = end date. When it's more than that it should
erase the date. So the result should be a list of trading days between and
including the start and end dates.

However, my code only puts a counter from 2 to 20 - how to you make it from
2 to infinity though? Then when i execute this code, it only copies the start
date and doesn't do anything beyond that. Could you guys help me pls?

Thanks.

Sub click()

Dim counter As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate

For counter = 2 To 20
curCell = Worksheets("sheet3").Cells(counter, 3).FormulaR1C1 =
"=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(counter, 3) = ""
Next counter

End Sub

and then i increased the range to 555, so for counter=2 to 555, and the list
went beyond the end date until today's date and then a bunch of #VALUE!
thereafter, so my endDate setting doesn't work...

here's a piece of it

2/2/2006
2/3/2006
2/6/2006
2/7/2006
2/8/2006
2/9/2006
2/10/2006
2/13/2006
2/14/2006
2/15/2006
2/16/2006
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!


"alexandraVBAgirl" wrote:

wait i made it work for this range for this range with the following code, my
question is how do i make the code maybe more efficient and not limited from
2 to 20 but from 2 to infinity?

Sub click()

Dim counter As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate

For counter = 2 To 20
curCell = Worksheets("sheet3").Cells(counter, 3).Select
ActiveCell.FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
If curCell endDate Then Cells(counter, 3) = ""
Next counter

End Sub

worksheet effect

1/1/2005 start date 1/1/2005
1/3/2005 end date 2/2/2006
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/10/2005
1/11/2005
1/12/2005
1/13/2005
1/14/2005
1/17/2005
1/18/2005
1/19/2005
1/20/2005
1/21/2005
1/24/2005
1/25/2005
1/26/2005
1/27/2005


"alexandraVBAgirl" wrote:

Here is the excel ws and the code follows.

1/1/2005 start date 1/1/2005
end date 2/2/2006

This code is supposed to copy the start date, then extract the next trading
days date from DVS referencing the start date through the formula, and do so
until the trading day date = end date. When it's more than that it should
erase the date. So the result should be a list of trading days between and
including the start and end dates.

However, my code only puts a counter from 2 to 20 - how to you make it from
2 to infinity though? Then when i execute this code, it only copies the start
date and doesn't do anything beyond that. Could you guys help me pls?

Thanks.

Sub click()

Dim counter As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate

For counter = 2 To 20
curCell = Worksheets("sheet3").Cells(counter, 3).FormulaR1C1 =
"=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(counter, 3) = ""
Next counter

End Sub

hello again,

2 to infinity is a ridiculous concept to encorporate into your code as
it will loop foreva and the program will crash - instead you can do
this (i've modded ur code);

Code:
--------------------

Sub click()

Dim i%
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate
Sheets("Sheet3").Select

i =2
do
curCell = Cells(i, 3).FormulaR1C1 =
"=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(i, 3) = ""
i = i +1
loop until Cells(i, 3).Value = ""

End Sub

--------------------


This will loop through all of your sells containing data until it
reaches the last row containing no data - not infinity as it will
eventually end.

:)


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=513637

Thanks for ur reply! So your exact code didn't work beyond inputting the
start date into the list. Then i modified it slightly to this (see stuff
after Do)

Sub help()

Dim i%
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate
Sheets("Sheet4").Select

i = 2
Do
curCell = Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(i, 3) = ""
i = i + 1
Loop Until Cells(i, 3).Value = ""

End Sub

and it gave the start date of the list and the 2nd date but nothing beyond :(

What else could be wrong???

Thanks so much.

"gti_jobert" wrote:


hello again,

2 to infinity is a ridiculous concept to encorporate into your code as
it will loop foreva and the program will crash - instead you can do
this (i've modded ur code);

Code:
--------------------

Sub click()

Dim i%
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

startDate = Range("e1").Value
endDate = Range("e2").Value

Range("C1").Select
ActiveCell.FormulaR1C1 = startDate
Sheets("Sheet3").Select

i =2
do
curCell = Cells(i, 3).FormulaR1C1 =
"=dvshandelsdatum(R[-1]C,1)"

If curCell endDate Then Cells(i, 3) = ""
i = i +1
loop until Cells(i, 3).Value = ""

End Sub

--------------------


This will loop through all of your sells containing data until it
reaches the last row containing no data - not infinity as it will
eventually end.

:)


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=513637