bonjour,
je viens de cogner en plein sur un problème très embettant:
avec ma macro ci dessous, j'ai réussi a avoir l'erreur: Excel ne peut pas terminer cette tâche avec les ressources disponibles ...
c'est la catastrophe
je n'avais jamais rencontré ce problème car la base de données ne depassait jamais 65000 lignes et là j'ai une base de donnée de + de 100 000 lignes
resultat ce message arrive
je sais pas comment faire pour régler ce problème
quelqu'un arait il une idee?
Merci a tous
Sub lancement()
Dim CEL As Range
Application.ScreenUpdating = False
Sheets("error-report").Activate
Range("a5").Select
Selection.AutoFilter
Range("f5") = "results"
Range("g5") = "location"
Range("h5") = "incorrect information"
Range("i5") = "la valeur sur amazon"
Range("j5") = "doublon"
Range("e6:e" & Range("e300000").End(xlUp).Row).Select
For Each CEL In Selection
CEL.Value = Replace(CEL.Value, """", "")
CEL.Value = Replace(CEL.Value, "[", "")
CEL.Value = Replace(CEL.Value, "]", "")
Next
'---------------------------------------------
Range("h6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]="""","""",IF(RC[-2]=""a ignorer"","""",INDEX(data!R3:R1048576,MATCH(RC[-6],data!R3C1:R1048576C1,0),IFNA(MATCH(RC[-1],data!R2,0),MATCH(RC[-1],data!R3,0)))))"
If Range("a7") <> "" Then
Range("h6").AutoFill Range("h6:h" & Range("a300000").End(xlUp).Row)
Else
End If
'---------------------------------------------
Range("j6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""Error"",IF(RC[-4]="""",1,""""),"""")"
If Range("a7") <> "" Then
Range("j6").AutoFill Range("j6:j" & Range("a300000").End(xlUp).Row)
End If
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(COUNTIF(R6C[-9]:RC2,RC2)=1,RC[-7]=""Error""),1,0)"
If Range("a7") <> "" Then
Range("k6").AutoFill Range("k6:k" & Range("a300000").End(xlUp).Row)
End If
'---------------------------------------------
Range("d6:d" & Range("d300000").End(xlUp).Row).Select
For Each CEL In Selection
CEL.Value = Replace(CEL.Value, "Erreur", "Error")
Next
'--------------------------------------------STATS
Sheets("stats").Activate
Sheets("stats").Range("e33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-1]=""(blank)"","""",IF(RC[-1]=""(vide)"","""",IF(RC[-1]=""Total Général"","""",IF(R1C[32]=""Francais"",VLOOKUP(RC[-1],list_of_errors!R3C:R4998C[1],2,0),IF(R1C[32]=""english"",VLOOKUP(RC[-1],list_of_errors!R3C[2]:R4998C[3],2,0),IF(R1C[32]=""italia"",VLOOKUP(RC[-1],list_of_errors!R3C[4]:R4998C[5],2,0),"""")))))))"
Range("e33").Select
Selection.AutoFill Destination:=Range("e33:e150")
Sheets("stats").Activate
Range("K8").Select
ActiveCell.FormulaR1C1 = "=SUM('error-report'!R[-2]C:R[1048568]C)"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTA(data!R[-4]C[-11]:R[1048568]C[-11])=0,""unknown"",COUNTA(data!R[-4]C[-11]:R[1048568]C[-11]))"
Range("AW6").Select
ActiveCell.FormulaR1C1 = "=stats!R[2]C[-36]"
Range("AW7").Select
ActiveCell.FormulaR1C1 = "=((R[-2]C-R[-3]C)/5)+R[-3]C"
Range("AW8").Select
ActiveCell.FormulaR1C1 = "=((R[-3]C-R[-4]C)*2/5)+R[-4]C"
Range("AW9").Select
ActiveCell.FormulaR1C1 = "=((R[-4]C-R[-5]C)*3/5) + R[-5]C"
Range("AW10").Select
ActiveCell.FormulaR1C1 = "=((R[-5]C-R[-6]C)*4/5) + R[-6]C"
Range("AX4").Select
ActiveCell.FormulaR1C1 = "0"
Range("AX5").Select
ActiveCell.FormulaR1C1 = "180"
Range("AX6").Select
ActiveCell.FormulaR1C1 = "=((RC[-1]-R[-2]C[-1])/(R[-1]C[-1]-R[-2]C[-1]))*180"
Range("AZ4").Select
ActiveCell.FormulaR1C1 = "=50-(50*COS(RADIANS(R[2]C[-2])))"
Range("AZ5").Select
ActiveCell.FormulaR1C1 = "=50-(2*COS(RADIANS(R[1]C[-2]+90)))"
Range("AZ6").Select
ActiveCell.FormulaR1C1 = "=50-(2*COS(RADIANS(RC[-2]-90)))"
Range("AZ7").Select
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("AZ8").Select
ActiveCell.FormulaR1C1 = "50"
Range("BA4").Select
ActiveCell.FormulaR1C1 = "=50*SIN(RADIANS(R[2]C[-3]))"
Range("BA5").Select
ActiveCell.FormulaR1C1 = "=2*SIN(RADIANS(R[1]C[-3]+90))"
Range("BA6").Select
ActiveCell.FormulaR1C1 = "=2*SIN(RADIANS(RC[-3]-90))"
Range("BA7").Select
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("BA8").Select
ActiveCell.FormulaR1C1 = "0"
Range("BA9").Select
Sheets("error-report").Select
Application.ScreenUpdating = True
MENU.Show
End Sub
je viens de cogner en plein sur un problème très embettant:
avec ma macro ci dessous, j'ai réussi a avoir l'erreur: Excel ne peut pas terminer cette tâche avec les ressources disponibles ...
c'est la catastrophe
je n'avais jamais rencontré ce problème car la base de données ne depassait jamais 65000 lignes et là j'ai une base de donnée de + de 100 000 lignes
resultat ce message arrive
je sais pas comment faire pour régler ce problème
quelqu'un arait il une idee?
Merci a tous
Sub lancement()
Dim CEL As Range
Application.ScreenUpdating = False
Sheets("error-report").Activate
Range("a5").Select
Selection.AutoFilter
Range("f5") = "results"
Range("g5") = "location"
Range("h5") = "incorrect information"
Range("i5") = "la valeur sur amazon"
Range("j5") = "doublon"
Range("e6:e" & Range("e300000").End(xlUp).Row).Select
For Each CEL In Selection
CEL.Value = Replace(CEL.Value, """", "")
CEL.Value = Replace(CEL.Value, "[", "")
CEL.Value = Replace(CEL.Value, "]", "")
Next
'---------------------------------------------
Range("h6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]="""","""",IF(RC[-2]=""a ignorer"","""",INDEX(data!R3:R1048576,MATCH(RC[-6],data!R3C1:R1048576C1,0),IFNA(MATCH(RC[-1],data!R2,0),MATCH(RC[-1],data!R3,0)))))"
If Range("a7") <> "" Then
Range("h6").AutoFill Range("h6:h" & Range("a300000").End(xlUp).Row)
Else
End If
'---------------------------------------------
Range("j6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""Error"",IF(RC[-4]="""",1,""""),"""")"
If Range("a7") <> "" Then
Range("j6").AutoFill Range("j6:j" & Range("a300000").End(xlUp).Row)
End If
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(COUNTIF(R6C[-9]:RC2,RC2)=1,RC[-7]=""Error""),1,0)"
If Range("a7") <> "" Then
Range("k6").AutoFill Range("k6:k" & Range("a300000").End(xlUp).Row)
End If
'---------------------------------------------
Range("d6:d" & Range("d300000").End(xlUp).Row).Select
For Each CEL In Selection
CEL.Value = Replace(CEL.Value, "Erreur", "Error")
Next
'--------------------------------------------STATS
Sheets("stats").Activate
Sheets("stats").Range("e33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC[-1]=""(blank)"","""",IF(RC[-1]=""(vide)"","""",IF(RC[-1]=""Total Général"","""",IF(R1C[32]=""Francais"",VLOOKUP(RC[-1],list_of_errors!R3C:R4998C[1],2,0),IF(R1C[32]=""english"",VLOOKUP(RC[-1],list_of_errors!R3C[2]:R4998C[3],2,0),IF(R1C[32]=""italia"",VLOOKUP(RC[-1],list_of_errors!R3C[4]:R4998C[5],2,0),"""")))))))"
Range("e33").Select
Selection.AutoFill Destination:=Range("e33:e150")
Sheets("stats").Activate
Range("K8").Select
ActiveCell.FormulaR1C1 = "=SUM('error-report'!R[-2]C:R[1048568]C)"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTA(data!R[-4]C[-11]:R[1048568]C[-11])=0,""unknown"",COUNTA(data!R[-4]C[-11]:R[1048568]C[-11]))"
Range("AW6").Select
ActiveCell.FormulaR1C1 = "=stats!R[2]C[-36]"
Range("AW7").Select
ActiveCell.FormulaR1C1 = "=((R[-2]C-R[-3]C)/5)+R[-3]C"
Range("AW8").Select
ActiveCell.FormulaR1C1 = "=((R[-3]C-R[-4]C)*2/5)+R[-4]C"
Range("AW9").Select
ActiveCell.FormulaR1C1 = "=((R[-4]C-R[-5]C)*3/5) + R[-5]C"
Range("AW10").Select
ActiveCell.FormulaR1C1 = "=((R[-5]C-R[-6]C)*4/5) + R[-6]C"
Range("AX4").Select
ActiveCell.FormulaR1C1 = "0"
Range("AX5").Select
ActiveCell.FormulaR1C1 = "180"
Range("AX6").Select
ActiveCell.FormulaR1C1 = "=((RC[-1]-R[-2]C[-1])/(R[-1]C[-1]-R[-2]C[-1]))*180"
Range("AZ4").Select
ActiveCell.FormulaR1C1 = "=50-(50*COS(RADIANS(R[2]C[-2])))"
Range("AZ5").Select
ActiveCell.FormulaR1C1 = "=50-(2*COS(RADIANS(R[1]C[-2]+90)))"
Range("AZ6").Select
ActiveCell.FormulaR1C1 = "=50-(2*COS(RADIANS(RC[-2]-90)))"
Range("AZ7").Select
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("AZ8").Select
ActiveCell.FormulaR1C1 = "50"
Range("BA4").Select
ActiveCell.FormulaR1C1 = "=50*SIN(RADIANS(R[2]C[-3]))"
Range("BA5").Select
ActiveCell.FormulaR1C1 = "=2*SIN(RADIANS(R[1]C[-3]+90))"
Range("BA6").Select
ActiveCell.FormulaR1C1 = "=2*SIN(RADIANS(RC[-3]-90))"
Range("BA7").Select
ActiveCell.FormulaR1C1 = "=R[-3]C"
Range("BA8").Select
ActiveCell.FormulaR1C1 = "0"
Range("BA9").Select
Sheets("error-report").Select
Application.ScreenUpdating = True
MENU.Show
End Sub