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 c10 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
1004 - Application-Defined or Object-Defined Error - Help
NMM
Thank you very much Navajo, this worked perfectly!
Much appreciated
Joe
e-mos
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