Excel VBA :: Copying values using a loop & run time error 9

Question

I'm pretty new to VBA, so the following question should not be too difficult. But since I spent about one and a half hour about the code, I think I simply lack some basic understanding how it should look like so I thought it's worth posting it here.

What the code is doing is basically copying values from one range in one sheet to another range in another sheet in the same active workbook containing some kind of a schedule.

I get a "run-time error '9' subscript out of range" error message already in the very first run through the loop in the section

'Subject
Morning = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day)

I tried out the code in a blank workbook so the error should not be due to formatting (in case this might have been the cause). But if you need more information about the context etc. I can provide of course more details.

Thank you for your answers!

Patrick

Sub SetData_new()
' SubModule to copy the data from 'Master_Plan' to particular sheets

Dim CurrWS As Long, StartWS As Long, EndWS As Long
StartWS = Sheets("W_1").Index
EndWS = Sheets("L_1").Index
Dim Weeknr As Integer
Weeknr = 5
Dim Day As Long
Dim Morning As Range, Afternoon As Range, Noon As Range

For CurrWS = StartWS To EndWS

   'Set updated ranges
   Set Morning = Worksheets(CurrWS).Cells(4, 4)
   Set Afternoon = Worksheets(CurrWS).Cells(8, 4)
   Set Noon = Worksheets(CurrWS).Cells(12, 4)

    For Day = 3 To 21 Step 3
    'Subject
       Morning = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day)
       Afternoon = Worksheets("ExamSchedule").Cells(Weeknr + 4, Day)
       Noon = Worksheets("ExamSchedule").Cells(Weeknr + 6, Day)

     'Category
       Morning.Offset(0, 1) = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day + 1)
       Afternoon.Offset(0, 1).Value = Worksheets("ExamSchedule").Cells(Weeknr + 4, Day + 1)
       Noon.Offset(0, 1).Value = Worksheets("ExamSchedule").Cells(Weeknr + 6, Day + 1)

     'Type
       Morning.Offset(0, 2) = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day + 2)
       Afternoon.Offset(0, 2).Value = Worksheets("ExamSchedule").Cells(Weeknr + 4, Day + 2)
   Noon.Offset(0, 2).Value = Worksheets("ExamSchedule").Cells(Weeknr + 6, Day + 2)

     'Specifications
       Morning.Offset(0, 3) = Worksheets("ExamSchedule").Cells(Weeknr + 3, Day)
       Afternoon.Offset(0, 3).Value = Worksheets("ExamSchedule").Cells(Weeknr + 5, Day)
       Noon.Offset(0, 3).Value = Worksheets("ExamSchedule").Cells(Weeknr + 7, Day)

     'Increment
       Morning = Morning.Offset(12, 0)
       Afternoon = Afternoon.Offset(12, 0)
       Noon = Noon.Offset(12, 0)
     Next Day

    Wochennummer = Wochennummer + 9
Next CurrWS

Worksheets("ExamSchedule").Activate

End Sub

A couple of basic issues to fix first: you need to explicitly spcify type for each variable in a DIM (otherwise the variable will be a Variant) eg CurrWS` and 2) you should use Long rather than Integer unless you have a specific need for 16 bit numbers (its faster, VBA does a background conversion to Long anyway). — chris neilsen
– chris neilsen, Commented Jan 3, 2013 at 8:30
Are you sure you have a sheet called ExamSchedule? - watch out for spelling, spaces etc — chris neilsen
– chris neilsen, Commented Jan 3, 2013 at 8:32
Ok, thank you. So I changed the two code lines 'Dim CurrWS As Long, StartWS As Long, EndWS As Long' and 'Dim Morning As Range, Afternoon As Range, Noon As Range'. That should be correct, right? — Patrick
– Patrick, Commented Jan 3, 2013 at 9:39
The name is right, it's "ExamSchedule" and there are no white spaces at the beginning or the end. — Patrick
– Patrick, Commented Jan 3, 2013 at 9:40

chris neilsen · Accepted Answer · 2013-01-03 10:16:26Z

2

The error is due to the default Variant type for the unqualifies Dim

What happens is the line

Morning = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day)

puts the value from ExamSchedule into Morning and sets its type to whatever the cell value type is.

When the line

Morning.Offset(0, 1) = Worksheets("ExamSchedule").Cells(Weeknr + 2, Day + 1)

is run, Morning is not of type Range, so it has no Offset property and errors

Using

Dim Morning as Range

etc should solve your problems.

Edit

to simplify a bit, try this
Use a variable for the worksheet
Be explicit with Value references

Sub SetData_new()
    ' SubModule to copy the data from 'Master_Plan' to particular sheets
    Dim Wochennummer As Long ' unless this is a global variable?
    Dim CurrWS As Long, StartWS As Long, EndWS As Long
    StartWS = Sheets("W_1").Index
    EndWS = Sheets("L_1").Index
    Dim Weeknr As Long
    Weeknr = 5
    Dim Day  As Long
    Dim Morning As Range, Afternoon As Range, Noon As Range

    Dim ws As Worksheet
    Set ws = Worksheets("ExamSchedule")

    For CurrWS = StartWS To EndWS

       'Set updated ranges
       Set Morning = Worksheets(CurrWS).Cells(4, 4)
       Set Afternoon = Worksheets(CurrWS).Cells(8, 4)
       Set Noon = Worksheets(CurrWS).Cells(12, 4)

        For Day = 3 To 21 Step 3
        'Subject
           Morning.Value = ws.Cells(Weeknr + 2, Day).Value
           Afternoon.Value = ws.Cells(Weeknr + 4, Day).Value
           Noon.Value = ws.Cells(Weeknr + 6, Day).Value

         'Category
           Morning.Offset(0, 1).Value = ws.Cells(Weeknr + 2, Day + 1).Value
           Afternoon.Offset(0, 1).Value = ws.Cells(Weeknr + 4, Day + 1).Value
           Noon.Offset(0, 1).Value = ws.Cells(Weeknr + 6, Day + 1).Value

         'Type
           Morning.Offset(0, 2) = ws.Cells(Weeknr + 2, Day + 2)
           Afternoon.Offset(0, 2).Value = ws.Cells(Weeknr + 4, Day + 2)
           Noon.Offset(0, 2).Value = ws.Cells(Weeknr + 6, Day + 2)

         'Specifications
           Morning.Offset(0, 3).Value = ws.Cells(Weeknr + 3, Day).Value
           Afternoon.Offset(0, 3).Value = ws.Cells(Weeknr + 5, Day).Value
           Noon.Offset(0, 3).Value = ws.Cells(Weeknr + 7, Day).Value

         'Increment
           Morning.Value = Morning.Offset(12, 0).Value
           Afternoon.Value = Afternoon.Offset(12, 0).Value
           Noon.Value = Noon.Offset(12, 0).Value
         Next Day

        Wochennummer = Wochennummer + 9
    Next CurrWS

    ws.Activate
End Sub

edited Jan 3, 2013 at 10:16

answered Jan 3, 2013 at 10:08

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrick Over a year ago

Last question: I wrote my own version (quite similar to the code above) and for some reason, I get a "compile error: syntax error" at the lines starting with Morningand Afternoon but the line starting with Noon is fine for Excel in the following code:

Morning.Offset(0, 1).Value= ExamSchedule.Cells(Weeknr + 2, Day + 1).Value        Afternoon.Offset(0, 1).Value = ExamSchedule.Cells(Weeknr + 4, Day + 1).Value        Noon.Offset(0, 1).Value = ExamSchedule.Cells(Weeknr + 6, Day + 1).Value

Why? (I'm using Office for Mac 2011)

chris neilsen Over a year ago

@Patrick no idea (you fixed the Dim issue, right?). I use Windows, not Mac so that may be a factor, can't really help there

Patrick Over a year ago

Yeah, maybe the Office for Mac 2011 is the problem in this case... Actually, I simply manually rewrote the lines not working and then the error suddenly disappeared as it should be. Strange...

Patrick · Accepted Answer · 2013-01-03 11:54:22Z

For everyone interested, the version I finally implemented looks as below. It works definitely fine on Windows and should work on Mac as well:

Sub SetData_new_my()
' SubModule to copy the data from 'Master_Plan' to particular sheets

Dim CurrWS As Long, StartWS As Long, EndWS As Long, ScheduleWS As Long
StartWS = Sheets("W_1").Index
EndWS = Sheets("L_1").Index

Dim Weeknr As Long
Weeknr = 5
Dim Day As Long

Dim Morning As Range, Afternoon As Range, Noon As Range    
Dim ExamSchedule As Worksheet
Set ExamSchedule = Worksheets("ExamSchedule")
    
For CurrWS = StartWS To EndWS
   'Set updated ranges
   Set Morning = Worksheets(CurrWS).Cells(4, 4)
   Set Afternoon = Worksheets(CurrWS).Cells(8, 4)
   Set Noon = Worksheets(CurrWS).Cells(12, 4)

    For Day = 3 To 21 Step 3
    'Subject
       Morning.Value = ExamSchedule.Cells(Weeknr + 2, Day).Value
       Afternoon.Value = ExamSchedule.Cells(Weeknr + 4, Day).Value
       Noon.Value = ExamSchedule.Cells(Weeknr + 6, Day).Value

     'Category
       Morning.Offset(0, 1).Value = ExamSchedule.Cells(Weeknr + 4, Day + 1).Value
       Afternoon.Offset(0, 1).Value = ExamSchedule.Cells(Weeknr + 4, Day + 1).Value
       Noon.Offset(0, 1).Value = ExamSchedule.Cells(Weeknr + 6, Day + 1).Value

     'Type
       Morning.Offset(0, 2).Value = ExamSchedule.Cells(Weeknr + 1, Day + 2).Value
       Afternoon.Offset(0, 2).Value = ExamSchedule.Cells(Weeknr + 4, Day + 2).Value
       Noon.Offset(0, 2).Value = ExamSchedule.Cells(Weeknr + 6, Day + 2).Value

     'Specifications
       Morning.Offset(0, 3).Value = ExamSchedule.Cells(Weeknr + 3, Day).Value
       Afternoon.Offset(0, 3).Value = ExamSchedule.Cells(Weeknr + 5, Day).Value
       Noon.Offset(0, 3).Value = ExamSchedule.Cells(Weeknr + 7, Day).Value

     'Increment
       Set Morning = Morning.Offset(12, 0)
       Set Afternoon = Afternoon.Offset(12, 0)
       Set Noon = Noon.Offset(12, 0)
     Next Day

    Weeknr = Weeknr + 9
Next CurrWS

ExamSchedule.Activate
      
End Sub

Collectives™ on Stack Overflow

Excel VBA :: Copying values using a loop & run time error 9

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related