"Subscript out of Range" Error Msg?

[mariasa]

Hi guys,
could you pls have a look at my code and let me know why i keep getting this error msg every time i try to execute it?

Thanks so much,
Maria

Sub CommandButtonDRG_Click()


Dim i As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

Dim SheetNumber As Integer

SheetNumber = "949"

startDate = Sheets(SheetNumber).Range("c2").Value
endDate = Sheets(SheetNumber).Range("e2").Value
Sheets(SheetNumber).Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Sheets(SheetNumber).Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Sheets(SheetNumber).Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
End

End Sub

[Ardus Petus]

Dim SheetNumber As String

HTH
--
AP

"mariasa" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Hi guys,
> could you pls have a look at my code and let me know why i keep getting
> this error msg every time i try to execute it?
>
> Thanks so much,
> Maria
>
> Sub CommandButtonDRG_Click()
>
>
> Dim i As Integer
> Dim curCell As Date
> Dim startDate As Date
> Dim endDate As Date
>
> Dim SheetNumber As Integer
>
> SheetNumber = "949"
>
> startDate = Sheets(SheetNumber).Range("c2").Value
> endDate = Sheets(SheetNumber).Range("e2").Value
> Sheets(SheetNumber).Columns("C:C").Select
> Selection.NumberFormat = "m/d/yyyy"
>
> Sheets(SheetNumber).Range("c2").Select
> ActiveCell.FormulaR1C1 = startDate
>
> i = 3
> Do
> curCell = Sheets(SheetNumber).Cells(i, 3).Select
> ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
> i = i + 1
> Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
> End
>
> End Sub
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile:
http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=523076
>

[mariasa]

Thanks, but I tried that, and now I get the "Application-defined or object-defined error" msg :-(

[Don Guillett]

To answer your question, this would be better.
Sub ft()
Set SheetNumber = Sheets("sheet1")
MsgBox SheetNumber.Range("a1")
End Sub

But you can't select the way you did. try something like
with sheets("sheet1")
startDate = .Range("c2")
endDate = .Range("e2")
..Columns("C:C").NumberFormat = "m/d/yyyy"
..Range("c2")=startDate '???
end with



--
Don Guillett
SalesAid Software
[email protected]
"mariasa" <[email protected]> wrote in
message news:[email protected]...
>
> Hi guys,
> could you pls have a look at my code and let me know why i keep getting
> this error msg every time i try to execute it?
>
> Thanks so much,
> Maria
>
> Sub CommandButtonDRG_Click()
>
>
> Dim i As Integer
> Dim curCell As Date
> Dim startDate As Date
> Dim endDate As Date
>
> Dim SheetNumber As Integer
>
> SheetNumber = "949"
>
> startDate = Sheets(SheetNumber).Range("c2").Value
> endDate = Sheets(SheetNumber).Range("e2").Value
> Sheets(SheetNumber).Columns("C:C").Select
> Selection.NumberFormat = "m/d/yyyy"
>
> Sheets(SheetNumber).Range("c2").Select
> ActiveCell.FormulaR1C1 = startDate
>
> i = 3
> Do
> curCell = Sheets(SheetNumber).Cells(i, 3).Select
> ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
> i = i + 1
> Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
> End
>
> End Sub
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile:
> http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=523076
>

[Doug Glancy]

Try dimming it as a long and removing the quotes from around it (unless it's
really named "929").

hth,

Doug


"mariasa" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks, but I tried that, and now I get the "Application-defined or
> object-defined error" msg :-(
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile:
> http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=523076
>

[mariasa]

Thanks for the suggestion. I tried this, following ur advice. But it still gives me the error message - really annoying.

Best,
Maria

Sub CommandButtonDRG_Click()


Dim i As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date



With Sheets("943")


startDate = Range("c2").Value
endDate = Range("e2").Value
Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Cells(i - 1, 3).Value = endDate
End
End With


End Sub

[mariasa]

Actually doing it with the String object works /see code below. So I seem to be all set. Thanks, guys!!! :-)

Best,
Maria


Sub CommandButtonDRG_Click()


Dim i As Integer
Dim curCell As Date
Dim startDate As Date
Dim endDate As Date

Dim SheetNumber As String

SheetNumber = "943"

startDate = Sheets(SheetNumber).Range("c2").Value
endDate = Sheets(SheetNumber).Range("e2").Value
Sheets(SheetNumber).Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"

Sheets(SheetNumber).Range("c2").Select
ActiveCell.FormulaR1C1 = startDate

i = 3
Do
curCell = Sheets(SheetNumber).Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
i = i + 1
Loop Until Sheets(SheetNumber).Cells(i - 1, 3).Value = endDate
End

End Sub

[Don Guillett]

When you use WITH you need to use . in front of your ranges that apply to
the with

> startDate = Range("c2").Value
startDate = . Range("c2").Value

Also, you paid NO attention to the other suggestions I gave about selecting.

--
Don Guillett
SalesAid Software
[email protected]
"mariasa" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the suggestion. I tried this, following ur advice. But it
> still gives me the error message - really annoying.
>
> Best,
> Maria
>
> Sub CommandButtonDRG_Click()
>
>
> Dim i As Integer
> Dim curCell As Date
> Dim startDate As Date
> Dim endDate As Date
>
>
>
> With Sheets("943")
>
>
> startDate = Range("c2").Value
> endDate = Range("e2").Value
> Columns("C:C").Select
> Selection.NumberFormat = "m/d/yyyy"
>
> Range("c2").Select
> ActiveCell.FormulaR1C1 = startDate
>
> i = 3
> Do
> curCell = Cells(i, 3).Select
> ActiveCell.FormulaR1C1 = "=dvstradedate(R[-1]C,1)"
> i = i + 1
> Loop Until Cells(i - 1, 3).Value = endDate
> End
> End With
>
>
> End Sub
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile:
> http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=523076
>