Help needed with the list of the trading days between start/endDat

alexandraVBAgirl · 02-17-2006, 09:35 AM

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

alexandraVBAgirl · 02-17-2006, 09:55 AM

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

alexandraVBAgirl · 02-17-2006, 10:20 AM

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

**gti_jobert** · 02-17-2006, 11:19 AM

hello again,

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

Please Login or Register  to view this content.

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

alexandraVBAgirl · 02-17-2006, 12:25 PM

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
>
>