Erreur d’exécution

P

pascal82

XLDnaute Occasionnel
Bonjour à tous,

2 questions à vous proposer:
Les faits :
Erreur d’exécution a chaque lancement de la macro Test25 :
Voici le message : Erreur d’exécution ‘-2147417848 (800 10 108)’
La méthode ‘PasteSpecial de l’objet Range a échoué.
Et lors de la fermeture du classeur :
‘L’image est trop grande et va être tronquée’.

Question 1 :
Ou se trouve l’anomalie dans ce code ?

Question 2 :
Y aurait-il une âme charitable pour me simplifier « Test 14 », les formules sont longues et surcharge inutilement la feuille Excel.
Je peux joindre le fichier si nécessaire, par contre il faut m’expliquer comment envoyer un fichier > 40 k

Merci par avance.

PHP: 
Sub TEST25()
Deb = Timer
   Application.ScreenUpdating = False
      For I = 1 To 25
    decal = 25 * I
    Sheets("A").Range("A20:Y1139").Offset(0, decal).Copy
    Sheets("1").Activate
    Range("A20").PasteSpecial Paste:=xlPasteValues
    
    Application.Run "'Stade2&5.xlsm'!TestX14"
    
    Range("BA20:BA1139").Copy
    Range("DE20").Offset(0, I).PasteSpecial Paste:=xlPasteValues
    
     Range("BE17:CD17").Copy
     Range("EF1").Offset(I - 1, 0).PasteSpecial Paste:=xlPasteValues
  
  Next I
      
      Application.ScreenUpdating = True
     MsgBox "J'ai bossé " & Timer - Deb & " seconde"
     ActiveWorkbook.Save
     

End Sub

Sub TestX14()


Dim A As Long, X As Integer, Str_Val_1 As String, Cel As Range
For X = 1 To 25
    Select Case X
        Case 1
            Str_Val_1 = "=SIN(RC[-52]/R"
            Set Cel = Range("BE1")
        Case 2
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R"
            Set Cel = Range("BF1")
        Case 3
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R"
            Set Cel = Range("BG1")
        Case 4
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R"
            Set Cel = Range("BH1")
        Case 5
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R"
            Set Cel = Range("BI1")
        Case 6
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R"
            Set Cel = Range("BJ1")
        Case 7
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R"
            Set Cel = Range("BK1")
        Case 8
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R"
            Set Cel = Range("BL1")
        Case 9
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R"
            Set Cel = Range("BM1")
        Case 10
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R"
            Set Cel = Range("BN1")
        Case 11
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R"
            Set Cel = Range("BO1")
        Case 12
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R"
            Set Cel = Range("BP1")
        Case 13
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R"
            Set Cel = Range("BQ1")
        Case 14
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R"
            Set Cel = Range("BR1")
        Case 15
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R"
            Set Cel = Range("BS1")
        Case 16
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R"
            Set Cel = Range("BT1")
        Case 17
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R"
            Set Cel = Range("BU1")
        Case 18
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R"
            Set Cel = Range("BV1")
        Case 19
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R"
            Set Cel = Range("BW1")
        Case 20
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R"
            Set Cel = Range("BX1")
        Case 21
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R17C76)+SIN(RC[-32]/R"
            Set Cel = Range("BY1")
        Case 22
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R17C76)+SIN(RC[-32]/R17C77)+SIN(RC[-31]/R"
            Set Cel = Range("BZ1")
        Case 23
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R17C76)+SIN(RC[-32]/R17C77)+SIN(RC[-31]/R17C78)+SIN(RC[-30]/R"
            Set Cel = Range("CA1")
        Case 24
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R17C76)+SIN(RC[-32]/R17C77)+SIN(RC[-31]/R17C78)+SIN(RC[-30]/R17C79)+SIN(RC[-29]/R"
            Set Cel = Range("CB1")
        Case 25
            Str_Val_1 = "=SIN(RC[-52]/R17C57)+SIN(RC[-51]/R17C58)+SIN(RC[-50]/R17C59)+SIN(RC[-49]/R17C60)+SIN(RC[-48]/R17C61)+SIN(RC[-47]/R17C62)+SIN(RC[-46]/R17C63)+SIN(RC[-45]/R17C64)+SIN(RC[-44]/R17C65)+SIN(RC[-43]/R17C66)+SIN(RC[-42]/R17C67)+SIN(RC[-41]/R17C68)+SIN(RC[-40]/R17C69)+SIN(RC[-39]/R17C70)+SIN(RC[-38]/R17C71)+SIN(RC[-37]/R17C72)+SIN(RC[-36]/R17C73)+SIN(RC[-35]/R17C74)+SIN(RC[-34]/R17C75)+SIN(RC[-33]/R17C76)+SIN(RC[-32]/R17C77)+SIN(RC[-31]/R17C78)+SIN(RC[-30]/R17C79)+SIN(RC[-29]/R17C80)+SIN(RC[-28]/R"
            Set Cel = Range("CC1")
       
    End Select
    For A = 20 To 218
        Range("BA20").FormulaR1C1 = Str_Val_1 & A & "C56)"
        Range("BA20").AutoFill Destination:=Range("BA20:BA1139"), Type:=xlFillDefault
        Range("BA12").Copy
        Cel.Offset(A - 1, 0).PasteSpecial Paste:=xlPasteValues
    Next A
Next X

    
End Sub
 
Trier par date Trier par votes
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Erreur d’exécution

Bonjour,

modifie peut être ceci, si ton code se trouve dans un module de feuille.. :
Code: 
Sheets("A").Range("A20:Y1139").Offset(0, decal).Copy 
    Sheets("1").Range("A20").PasteSpecial Paste:=xlPasteValues

bon après midi
@+
 
Vote positif 0 Vote négatif

Discussions similaires

W
  • Question
Microsoft 365 Fusionne les cellules vides parmi celle avec des valeurs
Réponses
4
Affichages
248
woira95
W
L
  • Question
XL 2016 Macro et suppressions de lignes
Réponses
9
Affichages
629
fanfan38
fanfan38
Nicolas JACQUIN
  • Résolu(e)
XL 2021 Doublons, Colorer partie de ligne en fonction de valeur dans colonne défini
Réponses
2
Affichages
332
Nicolas JACQUIN
Nicolas JACQUIN
L
  • Question
XL 2013 comment tranferré de un coté a l autre voir fichier merci d avance en macro
Réponses
9
Affichages
454
lucetudie
L
F
  • Résolu(e)
XL 2021 Créer Macro permettant d'indexer des nouveaux clients ? [Résolu]
4 5 6
Réponses
82
Affichages
2 K
fanou06
F
R
  • Question
Microsoft 365 Automatisation calcul
Réponses
2
Affichages
546
Roran
R
A
  • Résolu(e)
Microsoft 365 Trop de caractères de continuité de ligne
Réponses
9
Affichages
543
TooFatBoy
TooFatBoy
T
  • Question
Microsoft 365 Problème affichage multipage dans un tabstrip
Réponses
1
Affichages
359
troki74
T
M
  • Question
XL 2016 Une p'tite aide pour le loto!
Réponses
10
Affichages
2 K
jimip
J
M
  • Question
XL 2013 VBA-Envoyer par email "Plage de cellules collée dans un nouveau classeur en gardant formules de certaines cellules
Réponses
2
Affichages
710
MONTREAL2020
M

Membres actuellement en ligne

4 membres et 47 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 493
Messages
2 088 956
Membres
103 990
dernier inscrit
lamiadebz