Run-time error '1004': Application-defined or object-defined error

[kaywin]

I am getting "Application-defined or object-defined " error with the following codeWritten in ThisWorkbook)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim flag As Variant
flag = False
Set rng = Worksheets("CIRC_BW").Range("A1", Range("A1").End(xlDown))
'Set rng = Worksheets("CIRC_BW").Range("A1:A65536")
For Each cell In rng
If Not IsEmpty(cell) And IsEmpty(cell.Offset(0, 6)) Then
cell.Offset(0, 6).Value = 0
flag = True
End If
Next
End Sub

Can anyone help on this.

Thanks
Kaywin

 

[Ruddles]

Works perfectly here - I get a nice set of zeroes in column G where there's something in A and G doesn't already have a value in it.

Does it give any clue as to which line it's objecting to?

 

[kaywin]

Thanks for your reply .
I am getting this error when i select some other sheet in the same workbook other than "CIRC_BW" and click on save .

Its giving error in the line
Set rng = Worksheets("CIRC_BW").Range("A1", Range("A1").End(xlDown))


Thanks
Kaywin

 

[Richard Schollar]

Hi

You need to fully qualify your ranges:

With Worksheets("CIRC_BW")
  Set rng = .Range("A1", .Range("A1").End(xlDown))
End With

Note the periods before each Range()

 

[kaywin]

Thanks a lot .It works fine...


Thanks
Kaywin

 

[Ruddles]

Grr, beat me to it!