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

A

alexandraVBAgirl

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
 
A

alexandraVBAgirl

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
 
A

alexandraVBAgirl

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 said:
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 said:
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
 
G

gti_jobert

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.

:)
 
A

alexandraVBAgirl

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top