Sheet code runtime error 1004

[MeaclH]

Hi all,

I had this code working earlier, however I changed the ranges and it does not seem to work any longer. I am encountering a "METHOD RANGE OF OBJECT WORKSHEET FAILED" error.

When I open the debugger the first If line of code is highlighted yellow.

Any suggestions would be much appreciated.

Thanks
Hayden

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Resp As String

If Target.CountLarge = 1 And Not Intersect(Target, Range("G10: T10 , G15: T15 , G20: T20 , G25: T25 , G30: T30 , G36: T36 , G41: T41 , G46: T46 , G51: T51 , G56: T56 , G61: T61 , G66: T66 , G71: T71 , G76: T76 , G81: T81 , G86: T86 , G91: T91 , G96: T96 , G101: T101 , G106: T106 , G111: T111 , G116: T116 , G121: T121 , G126: T126 , G131: T131 , G137: T137 , G142: T142 , G147: T147 , G152: T152 , G158: T158 , G163: T163 , G168: T168")) Is Nothing Then
    With Target
      Select Case .Value
        Case "SDO", "STFN", "CDO", "CTFN" '<- Add more trigger values here if required
          Resp = Application.InputBox("Please insert details of absenteeism", _
          Title:="Absenteeism Details")
          If Len(Resp) > 0 And Resp <> "False" Then
            If Not .Comment Is Nothing Then
              .Comment.Text .Comment.Text & vbLf & Resp
            Else
              .AddComment Text:=Resp
            End If
          End If
      End Select
    End With
  End If

If Target.CountLarge = 1 And Not Intersect(Target, Range("G10: T10 , G15: T15 , G20: T20 , G25: T25 , G30: T30 , G36: T36 , G41: T41 , G46: T46 , G51: T51 , G56: T56 , G61: T61 , G66: T66 , G71: T71 , G76: T76 , G81: T81 , G86: T86 , G91: T91 , G96: T96 , G101: T101 , G106: T106 , G111: T111 , G116: T116 , G121: T121 , G126: T126 , G131: T131 , G137: T137 , G142: T142 , G147: T147 , G152: T152 , G158: T158 , G163: T163 , G168: T168")) Is Nothing Then
    With Target
      Select Case .Value
        Case "B/OFF", "B/EDO" '<- Add more trigger values here if required
          Resp = Application.InputBox("Please insert details of DAO request to be marked unavailable on OFF/EDO.", _
          Title:="OFF/EDO Unavailability Details")
          If Len(Resp) > 0 And Resp <> "False" Then
            If Not .Comment Is Nothing Then
              .Comment.Text .Comment.Text & vbLf & Resp
            Else
              .AddComment Text:=Resp
            End If
          End If
      End Select
    End With
  End If
  
If Target.CountLarge = 1 And Not Intersect(Target, Range("G9:T108")) Is Nothing Then '<- Set relevant range in this line
    With Target
      If InStr(1, .Value, "?") > 0 Then
          Resp = Application.InputBox("Please enter details of when this shift extension was confirmed by the DAO. Including time and date and how it was confirmed", "DAO Shift Extension Confirmation", , , , , , 2)
          If Len(Resp) > 0 And Resp <> "False" Then
            If .Comment Is Nothing Then
              .AddComment Text:=Resp
            Else
              .Comment.Text .Comment.Text & vbLf & Resp
            End If
          End If
      End If
    End With
  End If
  
If Target.CountLarge = 1 And Not Intersect(Target, Range("G9:T108")) Is Nothing Then '<- Set relevant range in this line
    With Target
      If InStr(1, .Value, "OK") > 0 Then
          Resp = Application.InputBox("Please enter details of when this shift extension was confirmed by the DAO. Including time and date and how it was confirmed", "DAO Shift Extension Confirmation", , , , , , 2)
          If Len(Resp) > 0 And Resp <> "False" Then
            If .Comment Is Nothing Then
              .AddComment Text:=Resp
            Else
              .Comment.Text .Comment.Text & vbLf & Resp
            End If
          End If
      End If
    End With
  End If
  
If Target.CountLarge = 1 And Not Intersect(Target, Range("G9:T108")) Is Nothing Then '<- Set relevant range in this line
    With Target
      If InStr(1, .Value, "DEC") > 0 Then
          Resp = Application.InputBox("Please enter details of when this shift extension was declined by the DAO. Including time and date when it was declined", "DAO Shift Extension Declined", , , , , , 2)
          If Len(Resp) > 0 And Resp <> "False" Then
            If .Comment Is Nothing Then
              .AddComment Text:=Resp
            Else
              .Comment.Text .Comment.Text & vbLf & Resp
            End If
          End If
      End If
    End With
  End If
  
  
End Sub

 

[MeaclH]

Actually, I have solved this myself!
Thanks

 

[RoryA]

Feel free to share the solution in case it helps someone else in future.

 

[MeaclH]

Feel free to share the solution in case it helps someone else in future.

Hi Rory,
I adjusted the range to a single wider range. But I think the initial problem was the space I had typed in the range.
Cheers
Hayden