InputBox and For...Next Loop

[ryguy7272]

I have a simple macro which asks a user for two dates. The dates are input
into the sheet and that’s that. Pretty simple, really.

Option Explicit
Sub InputDates()
Dim xdate As Date
Dim ydate As Date
Range("B1") = ""
Range("B2") = ""

xdate = InputBox("Beginning Date?", "Beginning Date", vbOKCancel)
Sheets("Sheet1").Range("B1") = ydate
ydate = InputBox("End Date?", "End Date", vbOKCancel)
Sheets("Sheet1").Range("B2") = ydate

‘maybe something like this:
n = ydate - ydate
For i = 1 to n
..Range("A5") = .Range(“A4â€).Offset(1, 0)
Next n

End Sub

First, I keep getting a subscript out of range error on this line:
Sheets("Sheet1").Range("B1") = xdate
It worked several times, then stopped working all of a sudden. Also, I just
added the for…next loop as pseudo code; I’m not really sure how to set it up.

Also, I have a function in A4 which gives me the beginning date:
=B1

Also, I have many, many, many functions, starting in A5, and going down:
=IF(A4<$B$2,A4+1,"")


I am looking for a way to simplify this with a macro, so that the macro just
lists each date in Column A, and just goes down the whole date range. For
instance, if the beginning date was 1/1/2008 and the ending date was
12/30/2008, 365 rows would get filled with a day of the year in each.
Finally, starting in B4, I wanted to do a simple calculation, such as this:
=1-CHIDIST(A3,B3)

Basically, this is for a one-tailed probability of the chi-squared
distribution, measured over time.

I’d sincerely appreciate any input.

Regards,
Ryan--

 

[FSt1]

hi
try
Sheets("sheet1").range("B1").Value = xdate
and i don't think you need the dots in your for next loop unless you have a
with clause.
regards
FSt1

 

[ryguy7272]

This is what I’m working with now:
Option Explicit
Sub InputDates()
Dim xdate As Date
Dim ydate As Date
Dim n As Variant
Dim i As Variant
Range("B1") = ""
Range("B2") = ""
xdate = InputBox("Beginning Date?", "Beginning Date", vbOKCancel)
Sheets("sheet1").Range("B1").Value = xdate
ydate = InputBox("End Date?", "End Date", vbOKCancel)
Sheets("sheet1").Range("B2").Value = ydate

n = (ydate - xdate)
For i = 1 To n
Range("A5") = Range("A4").Offset(1, 0)
Next n

End Sub

I get a compile error and the line:
Next n
is highlighted.
Message Reads: Invalid next control variable reference

Thanks for any/all assistance.

 

[Mike H]

A stage at a time!! This will fill your start to end dates in b4 down, What
do we do next?

Sub InputDates()
Dim xdate As Date
Dim ydate As Date
Dim n As Integer
Range("B1") = ""
Range("B2") = ""

xdate = InputBox("Beginning Date?", "Beginning Date", vbOKCancel)
Sheets("Sheet1").Range("B1") = xdate
ydate = InputBox("End Date?", "End Date", vbOKCancel)
Sheets("Sheet1").Range("B2") = ydate
n = ydate - xdate
Range("A4").Value = xdate
Range("A4:A" & n + 4).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False
End Sub

Mike

 

[Per Jessen]

Hi

You are iterating over "i", so it should be "Next i", or just "Next".

//Per

 

[ryguy7272]

Yep, that's it!! Mike H, couldn't have done it without ya. Also, good
catch Per Jessen! I knew that; just feeling disengaged from work today.

This is the final result:
Option Explicit
Sub InputDates()
Dim xdate As Date
Dim ydate As Date
Dim n As Integer

Range("B1") = ""
Range("B2") = ""
Range("A4:A65536") = ""

xdate = InputBox("Beginning Date?", "Beginning Date", vbOKCancel)
Sheets("Lognormal Chart").Range("B1") = xdate
ydate = InputBox("End Date?", "End Date", vbOKCancel)
Sheets("Lognormal Chart").Range("B2") = ydate

n = ydate - xdate
Range("A4").Value = xdate
Range("A4:A" & n + 1).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False

End Sub

Regards,
Ryan--