Error 1004 - Application-defined or Object-defined error
Solved
Forp74
Posted messages
7
Status
Membre
-
Forp74 Posted messages 7 Status Membre -
Forp74 Posted messages 7 Status Membre -
Hello,
My setup: W10 - Excel 2016.
I am not a VBA Excel specialist, but I "tinker" a little bit. For several days now, I've been stuck on this darn error 1004 with a Range() instruction. I have searched the forum, tested many modifications, but nothing works, I still get this darn error.
If someone has a solution, it would help me.
What my program does. I search a file for rows of 4 fields, which I copy sorted onto a sheet. The second field can be common to several rows.
I must keep only the first 3 rows with the same second field. The 4th, 5th... are deleted.
If we encounter only two rows or a single row with this second field, we delete.
Here is the part of the code where it crashes. The error occurs on line 25. And since I couldn't test further, I suppose it would also crash on the other lines 39 and 47.
What I've done in terms of modifications:
1. I added the first line Worksheets(NoFeuille).Select, it didn't change anything. Knowing that NoFeuille is an integer variable defined beforehand.
2. On line 25, I added Worksheets(NoFeuille). before Range(...), it didn't change anything.
I'm going in circles. Thank you for the help.
My setup: W10 - Excel 2016.
I am not a VBA Excel specialist, but I "tinker" a little bit. For several days now, I've been stuck on this darn error 1004 with a Range() instruction. I have searched the forum, tested many modifications, but nothing works, I still get this darn error.
If someone has a solution, it would help me.
What my program does. I search a file for rows of 4 fields, which I copy sorted onto a sheet. The second field can be common to several rows.
I must keep only the first 3 rows with the same second field. The 4th, 5th... are deleted.
If we encounter only two rows or a single row with this second field, we delete.
Here is the part of the code where it crashes. The error occurs on line 25. And since I couldn't test further, I suppose it would also crash on the other lines 39 and 47.
What I've done in terms of modifications:
1. I added the first line Worksheets(NoFeuille).Select, it didn't change anything. Knowing that NoFeuille is an integer variable defined beforehand.
2. On line 25, I added Worksheets(NoFeuille). before Range(...), it didn't change anything.
I'm going in circles. Thank you for the help.
Worksheets(NoFeuille).Select Range("G1").Select Dim n As Integer n = 0 ActiveCell.Offset(1, 0).Select n = n + 1 Do While ActiveCell.Offset(0, 0).Value <> "" ' Les 3 scores sont du même club If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(2, 1).Value Then ' On calcule le total des 3 scores et on le place dans la 1ère ligne du club ActiveCell.Offset(0, 4).Select ActiveCell.FormulaR1C1 = "=RC[-1]+R[1]C[-1]+R[2]C[-1]" 'ActiveCell.Offset(0, 4).Select ActiveCell.Offset(3, -4).Select n = n + 3 ' On recherche les éventuels 4e, 5e... scores pour ne pas les sélectionner For n = 0 To 50 ActiveCell.Offset(n, 0).Select If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value Then ' Suppression des celulles Worksheets(NoFeuille).Range(Cells(n, 7), Cells(n, 10)).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Else GoTo Suivant End If Next Suivant: Else ' Les lignes suivantes ne sont pas les 3 du même club ActiveCell.Offset(n, 0).Select ' Cas uniquement 2 lignes If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value And ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(2, 1).Value Then Range(Cells(n, 7), Cells(n + 1, 10)).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Else End If ' Cas uniquement 1 ligne If ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(1, 1).Value And ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(-1, 1).Value Then Range(Cells(n, 7), Cells(n, 10)).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Else End If End If ActiveCell.Offset(n, 0).Select Loop
3 réponses
Hello,
You need to review all your code and start by removing all the .Select and ActiveCell from your code; they are unnecessary and the primary source of problems.
Instead of selecting it, you just need to define the reference cell, for example:
--
Best regards
Patrice
You need to review all your code and start by removing all the .Select and ActiveCell from your code; they are unnecessary and the primary source of problems.
Instead of selecting it, you just need to define the reference cell, for example:
Dim cel As Range Set cel = Worksheets(NoFeuille).Range("G2") Do While cel.Value <> "" ' The 3 scores are from the same club If cel.Offset(0, 1).Value = cel.Offset(1, 1).Value And cel.Offset(0, 1).Value = cel.Offset(2, 1).Value Then ' .... --
Best regards
Patrice
