Subject: Your Help Needed with the Data List Generation

[mariasa]

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]

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 ?

 

[mariasa]

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

Could somebody check out my code and help me modify it so that it end
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 Su

 

[Dave Peterson]

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

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

 

[Dave Peterson]

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

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]

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