Runtime error '1004': Range class failed
Solved
Brainster
-
pilas31 Posted messages 1878 Status Contributor -
pilas31 Posted messages 1878 Status Contributor -
Hello,
I am a beginner in Excel programming and I can't seem to get past my problem.
Here is the situation:
I have 2 different files - File1 and File2.
In File1, I have a button that will execute a series of commands in File2.
To build the button code, I used a recorded macro.
This macro works very well when launched from File2. But when I execute it from my button in File1, it no longer works and I get this error:
Runtime error '1004':
The Select method of the Range class failed.
Here is my code:
Private Sub maj_Click()
Workbooks.Open Filename:= _
"P:\File1.xls"
If Range("M1") = "1" Then
Else
Rows("1:10000").Select
Selection.UnMerge
Range("M1").Select
ActiveCell.FormulaR1C1 = "1"
Range("C2:C10000").Select
Selection.Copy
Range("M2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R1C13=1,CONCATENATE(""L"",RC[10]),RC[10])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
Range("A1").Select
End If
Windows("File2.xls").Activate
...
etc.. the rest is correct.
The program stops at the first "Rows("1:10000").Select"
I then tried commenting this part out, but the error occurs next for the next "Range" and so on.
Do you have any idea?
Thank you very much!
p.s. the "File2" will be frequently deleted and updated with a new version. It is a sort of small database updated manually.
I am a beginner in Excel programming and I can't seem to get past my problem.
Here is the situation:
I have 2 different files - File1 and File2.
In File1, I have a button that will execute a series of commands in File2.
To build the button code, I used a recorded macro.
This macro works very well when launched from File2. But when I execute it from my button in File1, it no longer works and I get this error:
Runtime error '1004':
The Select method of the Range class failed.
Here is my code:
Private Sub maj_Click()
Workbooks.Open Filename:= _
"P:\File1.xls"
If Range("M1") = "1" Then
Else
Rows("1:10000").Select
Selection.UnMerge
Range("M1").Select
ActiveCell.FormulaR1C1 = "1"
Range("C2:C10000").Select
Selection.Copy
Range("M2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R1C13=1,CONCATENATE(""L"",RC[10]),RC[10])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
Range("A1").Select
End If
Windows("File2.xls").Activate
...
etc.. the rest is correct.
The program stops at the first "Rows("1:10000").Select"
I then tried commenting this part out, but the error occurs next for the next "Range" and so on.
Do you have any idea?
Thank you very much!
p.s. the "File2" will be frequently deleted and updated with a new version. It is a sort of small database updated manually.
Configuration: Windows XP Internet Explorer 7.0
3 answers
Hello,
Your code is associated with the button that is located in the sheet, so the problem likely comes from there (it's the same issue as if the macro were linked to a worksheet).
So I suggest the following solution:
You should move all your processing into a macro module (in VBA by going to Insert/Module).
You can name your macro for example "Processing".
In your new module, you will have a macro that looks like this:
In your button code, simply call this macro. Your button code will look like this:
There you go, it should work ...
See you later
--
Best regards,
Your code is associated with the button that is located in the sheet, so the problem likely comes from there (it's the same issue as if the macro were linked to a worksheet).
So I suggest the following solution:
You should move all your processing into a macro module (in VBA by going to Insert/Module).
You can name your macro for example "Processing".
In your new module, you will have a macro that looks like this:
Sub Processing() Workbooks.Open Filename:= _ "P:\File1.xls" If Range("M1") = "1" Then ... Else ... Rows("1:10000").Select Selection.UnMerge ... End Sub In your button code, simply call this macro. Your button code will look like this:
Private Sub maj_Click() Call Processing End Sub
There you go, it should work ...
See you later
--
Best regards,
You just made my day, my week, and you’ve also reconciled me with programming in Excel! Your solution works wonderfully and has solved my problem!
It's not easy to learn this on my own and I thank you for your help. It is greatly appreciated.
I will try to help someone in the community in return!
Thanks again and have a great week!
I am marking the issue as resolved.
Best of luck.
--
Sincerely,