1004 - Application-Defined or Object-Defined Error - Help  
Author Message
JRawly





PostPosted: Visual Basic for Applications (VBA), 1004 - Application-Defined or Object-Defined Error - Help Top

I have an Excel workbook with 8 Sheets on it.

On Sheet6 there are a number of named ranges.

One of the ranges is: BreakTotal and the range is defined as: AuxTimesConv!$F$5:$F$100 where AuxTimeConv is the sheet name.

On Sheet1 (Named: Formatting) there is a command button.

Private Sub CommandButton1_Click()
On Error GoTo 10
'Lets start with the Breaks
Dim c As Range, r As Range
Dim x As Integer
Dim a As String
Dim n As String
x = 1
Set r = Range("BreakTotal")
For Each c In r
If c.Value = 0 Then
Else
a = c.Value
n = Worksheets(Sheet6).Range("A" & c.Row)
MsgBox n
End If
Next c

10 MsgBox Err.Description & Chr$(13) & Err.Source, vbInformation, Err.Number
Err.Clear
End Sub

The line in red is where the code is erroring with the 1004 error message. I have tried changing that line to "For each c in Range("BreakTotal")" but I get the same error message if anyone can help at all I would greatly appreciate it.

Thanks in advance,

Joe



Microsoft ISV Community Center Forums1  
 
 
Navajo





PostPosted: Visual Basic for Applications (VBA), 1004 - Application-Defined or Object-Defined Error - Help Top

Joe

I think you need to fully qualify the reference if the range referred to is not on the active sheet. The following should work:

Set r = ActiveWorkbook.Sheets("AuxTimesConv").Range("BreakTotal") 'fully qualify the reference to the named range
For Each c In r
If c.Value <> 0 Then
a = c.Value
n = Worksheets("AuxTimesConv").Range("A" & c.Row).Address
MsgBox n
End If
Next c


 
 
JRawly





PostPosted: Visual Basic for Applications (VBA), 1004 - Application-Defined or Object-Defined Error - Help Top

Thank you very much Navajo, this worked perfectly!

Much appreciated

Joe