none
1004 - Application-Defined or Object-Defined Error - Help

    Question

  • 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

    Monday, June 26, 2006 6:46 PM

Answers

  • 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

    Monday, June 26, 2006 11:21 PM

All replies

  • 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

    Monday, June 26, 2006 11:21 PM
  • Thank you very much Navajo, this worked perfectly!

    Much appreciated

    Joe

    Tuesday, June 27, 2006 3:18 PM
  • I have another Application Defined or Object Defined Error and I would love your help.

    On one workbook with 4 different tabs.  Each tab has that persons weekly schedule.  For each day of the schedule I have created a command button corresponding to a set print area that would print that persons Monday schedule, than Tuesday schedule, etc.

    I have a master page where one print master button will consolidate all the staffs schedule and print it on one sheet.

    This is terrifically complicated stuff and I am running into an error.  Below is the attached code.  Any help would be appreciated.

    Sub Wednesday_Print()
    '
    ' Wednesday_Print Macro
    '

    '
        ActiveCell.Offset(1, -4).Range("A1:F47").Select   (THIS AREA IS HIGHLIGHTED AS THE PROBLEM)
        ActiveSheet.PageSetup.PrintArea = "$O$2EmbarrassedT$48"
        ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    End Sub
    Thursday, July 17, 2008 1:07 PM
  • Hi

     

    The problem will be if the active cell is in column A to D. The offset -4 will give an invalid top left hand reference for the start of your range. Can you predict the active cell when the macro is called?

     

    Regards

     

    ADG

     

    Thursday, July 17, 2008 2:55 PM