|
InputBox and For...Next Loop
I have a simple macro which asks a user for two dates. The dates are input
into the sheet and thats 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 fornext loop as pseudo code; Im 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. Id sincerely appreciate any input. Regards, Ryan-- -- RyGuy |
InputBox and For...Next Loop
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" wrote: I have a simple macro which asks a user for two dates. The dates are input into the sheet and thats 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 fornext loop as pseudo code; Im 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. Id sincerely appreciate any input. Regards, Ryan-- -- RyGuy |
InputBox and For...Next Loop
This is what Im 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. -- RyGuy "FSt1" wrote: 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" wrote: I have a simple macro which asks a user for two dates. The dates are input into the sheet and thats 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 fornext loop as pseudo code; Im 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. Id sincerely appreciate any input. Regards, Ryan-- -- RyGuy |
InputBox and For...Next Loop
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 "ryguy7272" wrote: This is what Im 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. -- RyGuy "FSt1" wrote: 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" wrote: I have a simple macro which asks a user for two dates. The dates are input into the sheet and thats 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 fornext loop as pseudo code; Im 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. Id sincerely appreciate any input. Regards, Ryan-- -- RyGuy |
InputBox and For...Next Loop
Hi
You are iterating over "i", so it should be "Next i", or just "Next". //Per "ryguy7272" skrev i en meddelelse ... 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. -- RyGuy "FSt1" wrote: 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" wrote: 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-- -- RyGuy |
InputBox and For...Next Loop
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-- -- RyGuy "Per Jessen" wrote: Hi You are iterating over "i", so it should be "Next i", or just "Next". //Per "ryguy7272" skrev i en meddelelse ... 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. -- RyGuy "FSt1" wrote: 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" wrote: 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-- -- RyGuy |
| All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com