locked
Run-time error '1004': Application-defined or object-defined error RRS feed

  • Question

  • i have never made a macro before.  Well i still haven't because i can't ge thtis macro to work.  I am trying to make a macro that opens each of the files the user selects, deletes the first row of each sheet, then saves and closes te workbook.  But i keep getting the error message "Run-Time Error 1004:  Application-defined or object-defined error".  I stole bits and pieces from other macros i've seen so sorry if this one is all choppy.  my macro is below..... i'd greatly appreciate any help

    Sub TopRowDelete3()

    '

    ' TopRowDelete3 Macro

    '

    Dim filestr As Variant

     

      filestr = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)

        'Tests the variable filestr to see if it is valid

         

          If filestr(1) = "False" Then

            MsgBox "No file specified.", vbExclamation, "Duh!!!"

            Exit Sub

           Else

            

           'Loops through every file that is selected and opens each one

           For Y = 1 To UBound(filestr)

              Workbooks.Open filestr(Y)

              Rows("1:1").Select

              Selection.Delete Shift:=xlUp

              ActiveWorkbook.Save

              ActiveWorkbook.Close

           Next

         

          End If

         

       End Sub

     

    Tuesday, July 19, 2011 1:12 PM

Answers

  • When you use unqualified range objects like you do with the cells() reference,
    those cells belong to the activesheet (if the code is in a general module) or
    the sheet that owns the code (if the code is in a worksheet module).
     
    You can qualify the ranges with something like:
     
    Worksheets("Data Table").Range(Worksheets("Data Table").Cells((myRowCount * S +
    2), 14), Worksheets("Data Table").Cells((myRowCount * (S + 1) + 2), 14)).Value =
    Worksheets("Hours").Range(Worksheets("Hours").Cells(4, (myColumnCount + 10 -
    S)), Worksheets("Hours").Cells((myRowCount + 4), (myColumnCount + 10 -
    S))).Value
     
    Or you could some variables and a with/endwith statement:
     
    Dim DTWks as worksheet
    dim HWks as worksheet
    Dim DTRng as range
    dim HRng as range
    set dtwks = worksheets("Data Table")
    set HWks = worksheets("Hours")
     
    with DTWks
     set dtrng = .range(.cells(myrowcount * s+2,14), _
                        .cells(myrowcount * (s+1)+2),14)
    end with
     
    with hwks
      set hrng = .range(.cells(4,mycolumncount+10-s), _
                        .cells(myrowcount+4, mycolumncount+10-S))
    end with
     
    dtwks.value = hrng.value
     
    All untested, uncompiled -- watch for typos!       
       hwks.range(hwks
     
    DutchOven8 wrote:
    >
    > Can somebody please explain why I get a runtime error on the following:
    >
    >
    >
    >         'CopyHoursData
    > Worksheets("Data Table").Range(Cells((myRowCount * S + 2), 14),
    > Cells((myRowCount * (S + 1) + 2), 14)).Value =
    > Worksheets("Hours").Range(Cells(4, (myColumnCount + 10 - S)),
    > Cells((myRowCount + 4), (myColumnCount + 10 - S))).Value
    >
    > Thanks so much for any help you offer
     
    --
     
    Dave Peterson
     
    • Proposed as answer by DutchOven8 Friday, January 13, 2012 3:04 PM
    • Marked as answer by danishani Friday, January 13, 2012 3:43 PM
    Friday, January 13, 2012 12:28 PM
  • In the VBE, select your workbook, and use Insert    Module.   That is where the code needs to go. 

    You may have been pasting your code into either a sheet module or the ThisWorkbook object.

    If you still have problems, contact me at

    bdeitrick at alum dot mit dot edu

    and I will send you a working example workbook.


    HTH, Bernie

    • Marked as answer by danishani Friday, January 13, 2012 3:11 AM
    Friday, July 22, 2011 2:39 PM
  • check this code, I tested and works ok
    Sub TopRowDelete3()
    
    '
    ' TopRowDelete3 Macro
    '
    Dim filestr As Variant
      
     filestr = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
      'Tests the variable filestr to see if it is valid
        
       If Not IsArray(filestr) Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
        Else
        
        'Loops through every file that is selected and opens each one
        For Y = LBound(filestr) To UBound(filestr)
         Workbooks.Open filestr(Y)
         Rows("1:1").Select
         Selection.Delete Shift:=xlUp
         ActiveWorkbook.Save
         ActiveWorkbook.Close
        Next
       
       End If
       
      End Sub
    

    • Marked as answer by danishani Friday, January 13, 2012 3:11 AM
    Tuesday, July 19, 2011 1:27 PM

All replies

  • check this code, I tested and works ok
    Sub TopRowDelete3()
    
    '
    ' TopRowDelete3 Macro
    '
    Dim filestr As Variant
      
     filestr = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
      'Tests the variable filestr to see if it is valid
        
       If Not IsArray(filestr) Then
        MsgBox "No file specified.", vbExclamation, "Duh!!!"
        Exit Sub
        Else
        
        'Loops through every file that is selected and opens each one
        For Y = LBound(filestr) To UBound(filestr)
         Workbooks.Open filestr(Y)
         Rows("1:1").Select
         Selection.Delete Shift:=xlUp
         ActiveWorkbook.Save
         ActiveWorkbook.Close
        Next
       
       End If
       
      End Sub
    

    • Marked as answer by danishani Friday, January 13, 2012 3:11 AM
    Tuesday, July 19, 2011 1:27 PM
  • I still receive the same error when i try and run it.  Could one of my settings be incorrect or perhaps there is a specific reference that i need turned on that is not on?
    Friday, July 22, 2011 1:52 PM
  • The code that is posted above works perfectly - Did you copy and paste the code exactly, or did you try to edit your existing code?
    HTH, Bernie
    Friday, July 22, 2011 2:01 PM
  • I  copied and paste it over my existing macro.
    Friday, July 22, 2011 2:06 PM
  • I started fresh and put the above code into a new sheet and i recevied the error box that has Microsoft Visual Basic in the header the in the message box there is a red circle with a white X in it and it says "400" next to it.  What does that mean?

    Friday, July 22, 2011 2:11 PM
  • In the VBE, select your workbook, and use Insert    Module.   That is where the code needs to go. 

    You may have been pasting your code into either a sheet module or the ThisWorkbook object.

    If you still have problems, contact me at

    bdeitrick at alum dot mit dot edu

    and I will send you a working example workbook.


    HTH, Bernie

    • Marked as answer by danishani Friday, January 13, 2012 3:11 AM
    Friday, July 22, 2011 2:39 PM
  • I got it to work!!!! Thank you both so much for your help
    Sunday, August 7, 2011 5:49 PM
  • Can somebody please explain why I get a runtime error on the following:

     


            'CopyHoursData
    Worksheets("Data Table").Range(Cells((myRowCount * S + 2), 14), Cells((myRowCount * (S + 1) + 2), 14)).Value = Worksheets("Hours").Range(Cells(4, (myColumnCount + 10 - S)), Cells((myRowCount + 4), (myColumnCount + 10 - S))).Value

    Thanks so much for any help you offer

    Friday, January 13, 2012 2:21 AM
  • Hi DutchOven8,

     

    You might better post a new thread, allthough you might receive the same error, your cause might be different and you might get better response as well.

     

    I will close this thread by marking the answer.

     

    Thanks for your understanding.

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 13, 2012 3:10 AM
  • When you use unqualified range objects like you do with the cells() reference,
    those cells belong to the activesheet (if the code is in a general module) or
    the sheet that owns the code (if the code is in a worksheet module).
     
    You can qualify the ranges with something like:
     
    Worksheets("Data Table").Range(Worksheets("Data Table").Cells((myRowCount * S +
    2), 14), Worksheets("Data Table").Cells((myRowCount * (S + 1) + 2), 14)).Value =
    Worksheets("Hours").Range(Worksheets("Hours").Cells(4, (myColumnCount + 10 -
    S)), Worksheets("Hours").Cells((myRowCount + 4), (myColumnCount + 10 -
    S))).Value
     
    Or you could some variables and a with/endwith statement:
     
    Dim DTWks as worksheet
    dim HWks as worksheet
    Dim DTRng as range
    dim HRng as range
    set dtwks = worksheets("Data Table")
    set HWks = worksheets("Hours")
     
    with DTWks
     set dtrng = .range(.cells(myrowcount * s+2,14), _
                        .cells(myrowcount * (s+1)+2),14)
    end with
     
    with hwks
      set hrng = .range(.cells(4,mycolumncount+10-s), _
                        .cells(myrowcount+4, mycolumncount+10-S))
    end with
     
    dtwks.value = hrng.value
     
    All untested, uncompiled -- watch for typos!       
       hwks.range(hwks
     
    DutchOven8 wrote:
    >
    > Can somebody please explain why I get a runtime error on the following:
    >
    >
    >
    >         'CopyHoursData
    > Worksheets("Data Table").Range(Cells((myRowCount * S + 2), 14),
    > Cells((myRowCount * (S + 1) + 2), 14)).Value =
    > Worksheets("Hours").Range(Cells(4, (myColumnCount + 10 - S)),
    > Cells((myRowCount + 4), (myColumnCount + 10 - S))).Value
    >
    > Thanks so much for any help you offer
     
    --
     
    Dave Peterson
     
    • Proposed as answer by DutchOven8 Friday, January 13, 2012 3:04 PM
    • Marked as answer by danishani Friday, January 13, 2012 3:43 PM
    Friday, January 13, 2012 12:28 PM
  • Brilliant!

     

    DavePeterson, Thank you for your help! That works beautifully.

     

    -Jeff

    Friday, January 13, 2012 2:44 PM