Subject: Your Help Needed with the Data List Generation

Below is the code thats supposed to take the given start and end date and
list all the trading days in between (including the start and end dates).
The macro is taking the trading days data from DVS Reporter through a simple formula =DVStradingDay(the start date, 1 (means next one, if u put 0, it'll show the start date again).

Anyhow, my code gave the start date of the list and the 2nd date but nothing beyond . Can you help me with that please?

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

The worksheet view:

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

Thanks so much.

i realize u guys can't just copy and paste my code because of this stupid german function i have there, but theoretically could you please help me understand what prevents the code from spitting out the whole list as opposed to the two first values ?

At this point my code produces an unlimited number of trading days' dates starting from the startdate and ending in the overflow. Well it's certainly better than just the 2 trading days' dates, but still needs revision lol

Could somebody check out my code and help me modify it so that it ends executing when curCell=endDate? Thanks, guys!!!!!

Sub help()

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

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

Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

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

i = 2
Do
curCell = Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
i = i + 1
Loop Until Cells(i, 3).Value = Range("e2").Value

End Sub

I'm confused about what you're doing, but this may give you an idea:

Option Explicit
Sub helpMe()

Dim startDate As Date
Dim endDate As Date
Dim TotalDays As Long

startDate = Range("e1").Value
endDate = Range("e2").Value
TotalDays = endDate - stardate

With Range("B3")
.AutoFill Destination:=.Resize(TotalDays), Type:=xlFillDays
End With
With Range("C3")
.Resize(TotalDays).FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
End With

End Sub



mariasa wrote:
>
> At this point my code produces an unlimited number of trading days'
> dates starting from the startdate and ending in the overflow. Well it's
> certainly better than just the 2 trading days' dates, but still needs
> revision lol
>
> Could somebody check out my code and help me modify it so that it ends
> executing when curCell=endDate? Thanks, guys!!!!!
>
> Sub help()
>
> Dim i%
> Dim curCell As Date
> Dim startDate As Date
> Dim endDate As Date
>
> startDate = Range("e1").Value
> endDate = Range("e2").Value
>
> Columns("C:C").Select
> Selection.NumberFormat = "m/d/yyyy"
>
> Range("C1").Select
> ActiveCell.FormulaR1C1 = startDate
> Sheets("Sheet4").Select
>
> i = 2
> Do
> curCell = Cells(i, 3).Select
> ActiveCell.FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
> i = i + 1
> Loop Until Cells(i, 3).Value = Range("e2").Value
>
> End Sub
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=514369

--

Dave Peterson

Ps. I wouldn't use Help as the name of the macro.

mariasa wrote:
>
> Below is the code thats supposed to take the given start and end date
> and
> list all the trading days in between (including the start and end
> dates).
> The macro is taking the trading days data from DVS Reporter through a
> simple formula =DVStradingDay(the start date, 1 (means next one, if u
> put 0, it'll show the start date again).
>
> Anyhow, my code gave the start date of the list and the 2nd date but
> nothing beyond . Can you help me with that please?
>
> 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
>
> The worksheet view:
>
> 1/1/2005 start date 1/1/2005
> 1/3/2005 end date 2/2/2006
>
> Thanks so much.
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=514369

--

Dave Peterson

Thanks for the advice - With slight modifications your code works, but doesnt produce the desired result - read below.

Sub helpMe()

Dim startDate As Date
Dim endDate As Date
Dim TotalDays As Long

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

Range("e3").Value = TotalDays
Range("c2").FormulaR1C1 = startDate



'With Range("B3")
'.AutoFill Destination:=.Resize(TotalDays), Type:=xlFillDays
'End With

With Range("C3")
.Resize(TotalDays).FormulaR1C1 = "=dvshandelsdatum(R[-1]C,1)"
End With

End Sub

However, it doesn't do exactly what i want it to. Here is the worksheet result

start date 1/1/2005
1/1/2005 end date 1/10/2005
1/3/2005 total days 9
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/10/2005
1/11/2005
1/12/2005
1/13/2005
So it basically gives the number of dates equal to the number of days between start and end dates. But since the german formula gives only the trading days ie excluding weekends and major holidays, it doesnt stop at 1/10 but rather at 1/13.

However I was able to get the results I want by modifying my previous code namely

Option Explicit


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)"
i = i + 1
Loop Until Cells(i - 1, 3).Value = endDate
End


End Sub

And here is the worksheet result

2/6/2004 start date 2/6/2004
2/9/2004 end date 2/10/2004
2/10/2004


So Im finally all set with this question