0

I am getting a range of object worksheet failed error on the line denoted with ***. I cant figure it out because if I step through the code after I get the error message, it doesn't error out again. Does anything look wrong?

Sub BuildMonthCalender()

Dim wkb As Workbook, shift_lines As Worksheet, month_lines As Worksheet, inputs As Worksheet, schedule As Worksheet
Dim d As Integer, w As Integer, last_row As Double, shift_line As Double, shifts As Double, p As Double, cRow As Double, cCol As Double
Dim counter As Double, cell_value As String

Set wkb = Excel.Workbooks("Call Center Headcount Model.xlsm")

Set schedule = wkb.Worksheets("Schedule")
Set shift_lines = ThisWorkbook.Worksheets("Shift Lines")
Set month_lines = ThisWorkbook.Worksheets("Month Lines")
Set inputs = wkb.Worksheets("Inputs")

schedule.Range("A1:K100000").Clear

last_row = month_lines.Range("A" & Rows.Count).End(xlUp).Row

shift_line = 3
shifts = 3
cRow = 0
cCol = 2
p = 1

'For shift_line = 3 To last_row
With month_lines
    Do
        If month_lines.Cells(shift_line, 1).Value <> "" Then
            For shifts = shift_line To month_lines.Cells(shift_line, 1).CurrentRegion.Rows.Count + shift_line
                cell_value = month_lines.Cells(shifts, 1).Value
                If cell_value = "" Then
                    Exit For
                End If
                schedule.Cells(cRow + month_lines.Cells(shifts, 9).Value, cCol + month_lines.Cells(shifts, 8).Value) = month_lines.Cells(shifts, 7).Value

                *****schedule.Range(Cells(p, 1), Cells(p + 5, 1)) = month_lines.Cells(shifts, 3).Value**

                schedule.Range(Cells(p, 2), Cells(p + 5, 2)) = month_lines.Cells(shifts, 10).Value
            Next shifts
        cRow = cRow + 10
        p = p + 10
        End If
        shift_line = .Cells(shift_line, 1).End(xlDown).Row + 3
    Loop Until shift_line > last_row
End With
p = 0
    For p = 1 To last_row
            With schedule.Range(Cells(p, 3), Cells(p + 5, 9))
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
            End With
        p = p + 9
    Next p
MsgBox "Complete!"
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
user14296107
  • 15
  • 3
  • 3
    Your cells object are not fully qualified you may want to see [https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) If this solves your query then let me know and I will close this as a duplicate. – Siddharth Rout Sep 21 '20 at 07:42
  • I looked at that before posting, but I declare the sheet name before each address. Maybe becuase there within a "with" statement? i will run it without and see. – user14296107 Sep 21 '20 at 07:47
  • Here is an example. `schedule.Range(Cells(p, 1), Cells(p + 5, 1))` should be `schedule.Range(schedule.Cells(p, 1),schedule.Cells(p + 5, 1))` and so on... The `Cells(p, 1)` and `Cells(p + 5, 1)` are referring to the active sheet which may or may not be `schedule` – Siddharth Rout Sep 21 '20 at 07:52
  • I see. I will give it a try that way. You can close if and If it doesnt work I'll try again. Thank you for your help. – user14296107 Sep 21 '20 at 07:54

0 Answers0