Error: Object required
 

A bit confused as to the reason for this error. The code has not been
altered and has been running fine for a year or two.........

Public Sub NettRate()

Dim ws As Worksheet
Dim C As Range

For Each ws In Worksheets()
With ws
ws.Unprotect
ws.Select
If Not (UCase(.Name) = "MASTER" Or UCase _ (.Name) =
"SUMMARY" _
Or UCase(.Name) = "CONTENTS" Or _
UCase(.Name) = "COVER") Then

For Each C In Intersect _
(.Range("C:C"), _ ActiveSheet.UsedRange)
If Not IsEmpty(C) Then
If LCase(C.Value) = "qty" Then GoTo Line1
If IsNumeric(C.Value) Or LCase(C.Value) _
= "item" Then
C.Offset(0, 7).Value = _
Application.WorksheetFunction.Sum _
(.Range(C.Offset(0, 2), C.Offset(0, 6)))
Line1:
End If
End If
Next 'C
End If
End With
Next
End Sub

Error occurs on the line:

For Each C In Intersect.......

Regards.

Error: Object required
 

Hi Stuart,

This error would occur if there were no intersection between column C and
the used range: if, for example, the first modified cell was (say) D1.

Check the UsedRange address on the problematic sheet.


---
Regards,
Norman



"Stuart" wrote in message
...
A bit confused as to the reason for this error. The code has not been
altered and has been running fine for a year or two.........

Public Sub NettRate()

Dim ws As Worksheet
Dim C As Range

For Each ws In Worksheets()
With ws
ws.Unprotect
ws.Select
If Not (UCase(.Name) = "MASTER" Or UCase _ (.Name) =
"SUMMARY" _
Or UCase(.Name) = "CONTENTS" Or _
UCase(.Name) = "COVER") Then

For Each C In Intersect _
(.Range("C:C"), _ ActiveSheet.UsedRange)
If Not IsEmpty(C) Then
If LCase(C.Value) = "qty" Then GoTo Line1
If IsNumeric(C.Value) Or LCase(C.Value) _
= "item" Then
C.Offset(0, 7).Value = _
Application.WorksheetFunction.Sum _
(.Range(C.Offset(0, 2), C.Offset(0, 6)))
Line1:
End If
End If
Next 'C
End If
End With
Next
End Sub

Error occurs on the line:

For Each C In Intersect.......

Regards.

Error: Object required
 

Got it.
Many thanks,

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

This error would occur if there were no intersection between column C and
the used range: if, for example, the first modified cell was (say) D1.

Check the UsedRange address on the problematic sheet.


---
Regards,
Norman



"Stuart" wrote in message
...
A bit confused as to the reason for this error. The code has not been
altered and has been running fine for a year or two.........

Public Sub NettRate()

Dim ws As Worksheet
Dim C As Range

For Each ws In Worksheets()
With ws
ws.Unprotect
ws.Select
If Not (UCase(.Name) = "MASTER" Or UCase _ (.Name) =
"SUMMARY" _
Or UCase(.Name) = "CONTENTS" Or _
UCase(.Name) = "COVER") Then

For Each C In Intersect _
(.Range("C:C"), _ ActiveSheet.UsedRange)
If Not IsEmpty(C) Then
If LCase(C.Value) = "qty" Then GoTo Line1
If IsNumeric(C.Value) Or LCase(C.Value) _
= "item" Then
C.Offset(0, 7).Value = _
Application.WorksheetFunction.Sum _
(.Range(C.Offset(0, 2), C.Offset(0, 6)))
Line1:
End If
End If
Next 'C
End If
End With
Next
End Sub

Error occurs on the line:

For Each C In Intersect.......

Regards.