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.
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