"Object required" error

[dmars]

This execution error is one of the strangest ones I've seen in Excel VBA, though admittedly I haven't seen that many.

The "Object required" error occurs at the "Set datecell.Value2 = weekdays(wkdy)" statement. (in red font below)

I've tried several different ways of defining and declaring datecell and weekdays. They all get the "Object required" error on this statement, and looking at the datecell object's properties and the weekdays array, there just doesn't seem to be any good reason to get this error.

Sub InsertNewDays()

Dim down, count, wkdy, tasks As Range, datecell As Range, weekdays() As Variant

'ReDim weekdays(1 To 7)
weekdays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")

'set tasks to columns of tasks to copy
Set tasks = Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 1))
down = tasks.rows.count

'datecell has the weekday as the task and the comment w/date in it
Set datecell = ActiveCell.Offset(0, 1)
    
count = InputBox("How many days to insert?", , 2)

'set wkdy pointing to next weekday after given date
For wkdy = 0 To 6
    If weekdays(wkdy) = datecell.Value2 Then
        wkdy = wkdy + 1
        GoTo Insert
    End If
Next

Insert:
For i = 1 To count
    tasks.Copy
    ActiveCell.Insert Shift:=xlDown
    
    'put correct weekday in first task cell
    [COLOR=#ff0000]Set datecell.Value2 = weekdays(wkdy)[/COLOR]
    

         unfinished & untested
====================================================
          |                                |
          V                                V

    'edit & move comment w/date in it
    'datecell.Comment
    'if WorksheetFunction.EoMonth(StartDate, 0)
    
    'move ActiveCell to the next day
    ActiveCell.Offset(down, 0).Select
    
    'datecell has the weekday as the task and the comment w/date in it
    Set datecell = ActiveCell.Offset(i * down, 1)
    
Next
End Sub

Obviously, there is a reason for this error. Whether it's a good one or not remains to be seen, but in any case I'm not seeing what the problem is.

 

[Andrew Poulsom]

Try removing Set, which is required only for object assignments.

 

[dmars]

Try removing Set, which is required only for object assignments.

That's actually what I did first (and just tried again), and I get

"Run-time error '91':

Object variable or With block variable not set"

So I used Set because it said I didn't set it. Then I get the "Object required" error.

 

[Andrew Poulsom]

Please post your revised code that causes an error.

 

[dmars]

Whoops! I must be cross-eyed from trying so many things with this macro tonight. In the current version I had Set on the value assignment and no Set on the datecell definition statement, so that's why I got error 91. Switched around this appears to work just fine. So you're right, the Set caused the problem in the version I originally posted.

Thanks

 

[dmars]

Here's my code that works:

Sub InsertNewDays()

Dim down, count, wkdy, tasks As Range, datecell As Range, weekdays() As Variant

'ReDim weekdays(1 To 7)
weekdays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")

'set tasks to columns of tasks to copy
Set tasks = Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 1))
down = tasks.rows.count

'need to do wkdays assignment in a loop, Excel doesn't do it right by range assignment
'wkdays = Range("weekdays")
'For i = 1 To 7
'    wkdays(i) = weekdays(i)
'Next

'datecell has the weekday as the task and the comment w/date in it
Set datecell = ActiveCell.Offset(0, 1)
    
count = InputBox("How many days to insert?", , 2)

'set wkdy pointing to next weekday after start
For wkdy = 0 To 6
    If weekdays(wkdy) = datecell.Value2 Then
        wkdy = wkdy + 1
        GoTo Insert
    End If
Next

Insert:
For i = 1 To count
    tasks.Copy
    ActiveCell.Insert Shift:=xlDown
    'ActiveWindow.SmallScroll down:=down  <-from recorded macro
    
    'clear datecell
    'Set datecell = Nothing
    
    
    'put correct weekday in "get up" task
    datecell.Value2 = weekdays(wkdy)

~~~~~~~~untested code~~~~~~~~~~~~~~

End Sub

/[CODE]