Results 1 to 7 of 7

Thread: [RESOLVED] Excel: Runtime Error 1004 Application-defined or object-defined error

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Resolved [RESOLVED] Excel: Runtime Error 1004 Application-defined or object-defined error

    Hi, I'm having error at the red colour line, I have tried to google around but fail to find out the cause and solution, can anyone please guide me?

    Code:
    Sub Copy_Data(file_dir As Variant, file_mei As Variant)
    Dim shs As Object
    Dim x As Integer
    
    Workbooks.Open Filename:=file_dir & "\" & file_mei
        If Err.Number = 1004 Then 'If file not found
            MsgBox "File has not been created" & Chr(13) & file_mei & "Is Required"
            Exit Sub
        End If
    Windows(file_mei).Activate
    For Each shs In ActiveWorkbook.Sheets
        
        If Left(shs.Name, 5) <> "Chart" Then
          
        row6 = 6
        aaa = 0
        NumRows = 0
        
        'number of rows of data
        NumRows = shs.Range("E6", Range("E6").End(xlDown)).Rows.Count
        'Sheets(shs.Name).Range("E6").Select
        
            'to loop until end of row
            For x = 0 To NumRows
            'Do Until IsEmpty(ActiveCell)
                If shs.Range("E" & row6 + aaa).Value <> "" Then
                
                    bbb = shs.Range("E" & row6 + aaa).Value
                    ccc = shs.Name
                    Windows(myname).Activate
                        
                    Range("A" & aaa + 1).Value = bbb
                    Range("B" & aaa + 1).Value = ccc
                    Range("C" & aaa + 1).Value = file_mei
                    aaa = aaa + 1
                End If
                
            ActiveCell.Offset(1, 0).Select
            Next
            'Loop
        End If      ' End Check sheet name if equal = "Chart"
        Windows(file_mei).Activate
    Next
    Application.DisplayAlerts = False
    Windows(file_mei).Activate
    ActiveWorkbook.Close
    
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    you need to fully qualify the range, that is parameter to primary range, as it is now the secondary range is in the activesheet, therefore can not be a part of the range in shs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    1. Dim the shs as Worksheet
    2. change this line
    Code:
    NumRows = shs.Range("E" & shs.Rows.Count).End(xlup)).Row
    3. start the loop from 1 not 0
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  4. #4

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    Quote Originally Posted by westconn1 View Post
    you need to fully qualify the range, that is parameter to primary range, as it is now the secondary range is in the activesheet, therefore can not be a part of the range in shs

    hi, thanks for your reply and explanation.
    but i'm still not very clear, can you please explain in detail or provide some example?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    Quote Originally Posted by seenu_1st View Post
    1. Dim the shs as Worksheet
    2. change this line
    Code:
    NumRows = shs.Range("E" & shs.Rows.Count).End(xlup)).Row
    3. start the loop from 1 not 0
    hi, thanks for reply. i will have a try.

  6. #6
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    forget to tell, once the sheet found activate the sheet
    Code:
    shs.activate
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2009
    Posts
    104

    Re: Excel: Runtime Error 1004 Application-defined or object-defined error

    Quote Originally Posted by seenu_1st View Post
    1. Dim the shs as Worksheet
    2. change this line
    Code:
    NumRows = shs.Range("E" & shs.Rows.Count).End(xlup)).Row
    3. start the loop from 1 not 0
    hi seenu, thanks for your solution, the code is running fine
    now just pending with some logic error and I believe i can solve it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width