-
Jul 12th, 2012, 05:01 AM
#1
Thread Starter
Lively Member
[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
-
Jul 12th, 2012, 05:16 AM
#2
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
-
Jul 12th, 2012, 08:00 AM
#3
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
-
Jul 12th, 2012, 08:01 AM
#4
Thread Starter
Lively Member
Re: Excel: Runtime Error 1004 Application-defined or object-defined error
Originally Posted by westconn1
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?
-
Jul 12th, 2012, 08:04 AM
#5
Thread Starter
Lively Member
Re: Excel: Runtime Error 1004 Application-defined or object-defined error
Originally Posted by seenu_1st
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.
-
Jul 12th, 2012, 08:06 AM
#6
Re: Excel: Runtime Error 1004 Application-defined or object-defined error
forget to tell, once the sheet found activate the sheet
-
Jul 12th, 2012, 11:51 PM
#7
Thread Starter
Lively Member
Re: Excel: Runtime Error 1004 Application-defined or object-defined error
Originally Posted by seenu_1st
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|