macro qui plante ..que de temps en temps!

ph.vanne · 12 Mars 2009

bonjour,
jai une macro qui plante ,mais que de temps en temps...?
Je m’explique :

J’ai fait une feuille ou je saisie mes informations de facturation client et en cliquant sur un bouton je lance la macro suivante qui :

-copie la facture ainsi crée

-ouvre un fichier ''a'' ou je stock les factures

-lance une impression de la facture en deux ex.

-colle la facture (avec une mise en forme simplifiée) dans fichier ''a'':

(En incrémentant de 1 à chaque fois)

-referme le fichier et l’enregistre

- retourne sur la feuille de saisie et remet tout a zéro pour la prochaine fois….

Ça marche ‘’nickel’’ sauf de temps en temps…(je n’ai pas pu déterminer une fréquence : je peux faire 5 ou 6 factures a la suite sans problème, ou une factures et…. bug !)

Mais quand ça bug, ça bug vraiment !,
l'ecran est bloqué sur une vision apocalyptique ! de deux factures superposée et le pc est carrément bloqué et impossible de s’en sortir sans faire un alt. / ctrl /sup !

C’est un peu une ''usine a gaz’’ et ce n’est pas très ‘’propre’’… (Une succession de copie/ collé glanés ça et la sur le site xld, surement des doublons…) mais ça marche…enfin presque !

avez vous une idée?

jetted · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Pour mieux t'aider il serait preferable de nous donner un exemple de ton code.

Compte Supprimé 979 · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Bonjour,

lance la macro suivante qui :

-copie la facture ainsi crée
-ouvre un fichier ''a'' ou je stock les factures
-lance une impression de la facture en deux ex.
-colle la facture (avec une mise en forme simplifiée) dans fichier ''a'':
(En incrémentant de 1 à chaque fois)
-referme le fichier et l’enregistre
- retourne sur la feuille de saisie et remet tout a zéro pour la prochaine fois….

Oui d'accord mais elle est où justement ta macro

On veut bien t'aider, mais faut nous aider aussi

A+

ph.vanne · 12 Mars 2009

voila...la chose!!

Sub macro_impression_deux_pages_et_ajouter_1()

' Macro1 Macro
' Macro enregistrée le 04/10/2007 par UTILISATEUR

ActiveSheet.Unprotect ("mdp") 'deproteger la feuille'
Application.ScreenUpdating = False
Sheets("APERCU DE LA FACTURE").Visible = True
Sheets("A5").Visible = True

If Range("d9") = "" And Range("e9") = "" And Range("f9") = "" And Range("g9") = "" And Range("h9") = "" And Range("i9") = "" Then
MsgBox "nombre de chambre(s) occuppée(s)!!"
Exit Sub
End If

If Range("c2") = "" Then

MsgBox "Nom du client!"
Exit Sub
End If

If Range("c3") = "" Then

MsgBox "date d'arrivée!"
Exit Sub
End If

If Range("c4") = "" Then

MsgBox "date du départ!"
Exit Sub
End If

If Range("c5") = "" Then

MsgBox "nombre total de personne(s)!"
Exit Sub
End If

If Range("c5") * Range("c6") <> Range("j13") Then

MsgBox " vérifier le nombre total de personne(s)ou le type de chambre!"
Exit Sub
End If

If Range("d10") = "" And Range("e10") = "" And Range("f10") = "" And Range("g10") = "" And Range("h10") = "" And Range("i10") = "" Then
MsgBox "choix du tarif/hôtel!!"
Exit Sub
End If

If Range("i10") = "" And Range("i9") = 1 Then

MsgBox " la chambre n'existe pas!"
Exit Sub
End If

If Range("g10") = "" And Range("g9") = 1 Then

MsgBox " la chambre n'existe pas!"
Exit Sub
End If

If Range("d10") = "" And Range("d9") = 1 Then

MsgBox " la chambre n'existe pas!"
Exit Sub
End If

'gele l'ecran pendant le deroulement de la macro'

Application.ScreenUpdating = False

' ouvrir le dossier "factures" du mois en cour

Workbooks.Open Filename:="C:\Documents and Settings\Utilisateur\Mes documents\DOSSIERS HOTEL\SECRETARIAT\FACTURES HOTEL\factures sous excel\factures_mars_09.xls"

'revenir sur "ed de facture" et copier l'apercu de la facture

Windows("ed fact 2008-12.xls").Activate

Sheets("A5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Sheets("APERCU DE LA FACTURE").Select
Range("A1:G36").Select
Selection.Copy

' creer une nouvelle feuille dans le dossier archivage des factures '

Windows("factures_mars_09.xls").Activate
Sheets("0").Select
Sheets.Add

'mise en forme de la facture enregistrée
'

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1

Columns("A:A").ColumnWidth = 10.29
Columns("B:B").ColumnWidth = 30.29
Columns("C:C").ColumnWidth = 7.29
Columns("D

").ColumnWidth = 1.71
Columns("E:E").ColumnWidth = 12
Columns("F:F").ColumnWidth = 18
Columns("G:G").ColumnWidth = 8.57
Range("G3").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").ColumnWidth = 9.43
Range("F2").Select
Selection.NumberFormat = "d/m/yy;@"
Range("G3").Select
Selection.NumberFormat = "d/m;@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B9:B10").Select
Selection.NumberFormat = "d/m/yy;@"
Columns("D

").ColumnWidth = 2.29
ActiveWindow.SmallScroll Down:=9
Range("C29:C30").Select
Selection.Style = "Currency"
Range("F18:F35").Select
Selection.Style = "Currency"
ActiveWindow.SmallScroll Down:=-24
Windows("ed fact 2008-12.xls").Activate
Range("F3").Select
Windows("factures_mars_09.xls").Activate
Range("D1").Select
Selection.ClearContents
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("E8:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=3
Range("B13:F35").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Range("E15:F34,C29:C30").Select
Range("C29").Activate
Selection.Style = "Currency"
Selection.Style = "Currency"
Selection.NumberFormat = "#,##0.00 €"
End With
With Range("B9:B10").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Range("B11").Select
Selection.NumberFormat = "General"
Range("E3").Select
ActiveCell.FormulaR1C1 = "FACTURE N°"
Range("F3").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("B11").Select
Selection.NumberFormat = "General"" nuitée(s)"""
Range("G3").Select
Selection.ClearContents
Range("F2").Select
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 36.29
Columns("B:B").ColumnWidth = 38.14
Columns("B:B").ColumnWidth = 41.71
Columns("B:B").ColumnWidth = 40.71
Range("B9:B11").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("C5").Select
Columns("F:F").ColumnWidth = 15.29
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
Columns("A:A").ColumnWidth = 8.43
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End With
With Range("E8:F11").Select
Selection.Font.Bold = True
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:B7").Select
Selection.Font.Bold = True
Range("E2:F3").Select
Selection.Font.Bold = True
Cells.Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B4").Select
Columns("B:B").ColumnWidth = 44
End With

With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32.86
Range("B27").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=54).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=61).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

'nommer la feuille crée par le n° de la facture'

With Range("B1").Select
ActiveSheet.Name = Range("f3").Value

'on enregistre le dossier factures et on ferme'

With Windows("factures_mars_09.xls").Activate

ActiveWorkbook.Save

ActiveWindow.Close

'on retourne sur saisie des infos sur ed de facture'

Windows("ed fact 2008-12.xls").Activate
Range("B8").Select
Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select
End With

'incrementer de 1'

With Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select

Sheets("apercu de la facture").Range("f3").Value = Sheets("apercu de la facture").Range("f3").Value + 1

'et remetre à zero la feulle de saisie'
Range("C5").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
ActiveCell.FormulaR1C1 = ""
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
ActiveCell.FormulaR1C1 = ""
Range("D9").Select
ActiveCell.FormulaR1C1 = ""
Range("E9").Select
ActiveCell.FormulaR1C1 = ""
Range("F9").Select
ActiveCell.FormulaR1C1 = ""
Range("G9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("I9").Select
ActiveCell.FormulaR1C1 = ""
Range("J9").Select
ActiveCell.FormulaR1C1 = ""
Range("H18").Select
ActiveCell.FormulaR1C1 = ""
Range("G18").Select
ActiveCell.FormulaR1C1 = ""
Range("E18").Select
ActiveCell.FormulaR1C1 = ""
Range("D17").Select
ActiveCell.FormulaR1C1 = ""
Range("j16").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
Range("D10:I10").Select
Selection.ClearContents
Range("a1").Select
End With

Sheets("APERCU DE LA FACTURE").Visible = False
Sheets("A5").Visible = False
Application.ScreenUpdating = True
ActiveSheet.Protect ("mdp") 'deproteger la feuille'

End With

End Sub

tora · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

y a à boire et à manger pour au moins 500 personnes la dedans !

sorry j'ai pas pu m'en empecher ... hi hi ...

clair y'a du nettoyage à faire ...

keepcool183 · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Salut à tous,

Effectivement c'est énorme

un mélange d'enregistreur et de code à la main.

vbacrumble · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Bonsoir

Keepcool183

Une suggestion:

Tu devrais encadrer ton code avec les balises BB code

Tu rendras ainsi ton message plus agréable à lire.

keepcool183 · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Re Vbacrumble,

Euh...... ce n'est pas mon code

mais je veux bien le faire, et BTW structurer tout ça

Code:

Sub macro_impression_deux_pages_et_ajouter_1()
' Macro1 Macro
' Macro enregistrée le 04/10/2007 par UTILISATEUR
ActiveSheet.Unprotect ("mdp") 'deproteger la feuille'
Application.ScreenUpdating = False
Sheets("APERCU DE LA FACTURE").Visible = True
Sheets("A5").Visible = True
If Range("d9") = "" And Range("e9") = "" And Range("f9") = "" And Range("g9") = "" And Range("h9") = "" And Range("i9") = "" Then
MsgBox "nombre de chambre(s) occuppée(s)!!"
Exit Sub
End If
If Range("c2") = "" Then
MsgBox "Nom du client!"
Exit Sub
End If

If Range("c3") = "" Then
MsgBox "date d'arrivée!"
Exit Sub
End If

If Range("c4") = "" Then
MsgBox "date du départ!"
Exit Sub
End If

If Range("c5") = "" Then
MsgBox "nombre total de personne(s)!"
Exit Sub
End If


If Range("c5") * Range("c6") <> Range("j13") Then
MsgBox " vérifier le nombre total de personne(s)ou le type de chambre!"
Exit Sub
End If

If Range("d10") = "" And Range("e10") = "" And Range("f10") = "" And Range("g10") = "" And Range("h10") = "" And Range("i10") = "" Then
MsgBox "choix du tarif/hôtel!!"
Exit Sub
End If
If Range("i10") = "" And Range("i9") = 1 Then

MsgBox " la chambre n'existe pas!"
Exit Sub
End If

If Range("g10") = "" And Range("g9") = 1 Then
MsgBox " la chambre n'existe pas!"
Exit Sub
End If

If Range("d10") = "" And Range("d9") = 1 Then
MsgBox " la chambre n'existe pas!"
Exit Sub
End If

Application.ScreenUpdating = False

Workbooks.Open Filename:="C:\Documents and Settings\Utilisateur\Mes documents\DOSSIERS HOTEL\SECRETARIAT\FACTURES HOTEL\factures sous excel\factures_mars_09.xls"

Windows("ed fact 2008-12.xls").Activate

Sheets("A5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Sheets("APERCU DE LA FACTURE").Select
Range("A1:G36").Select
Selection.Copy

Windows("factures_mars_09.xls").Activate
Sheets("0").Select
Sheets.Add

'mise en forme de la facture enregistrée
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select
Columns("A:A").ColumnWidth = 10.29
Columns("B:B").ColumnWidth = 30.29
Columns("C:C").ColumnWidth = 7.29
Columns("D").ColumnWidth = 1.71
Columns("E:E").ColumnWidth = 12
Columns("F:F").ColumnWidth = 18
Columns("G:G").ColumnWidth = 8.57
Range("G3").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").ColumnWidth = 9.43
Range("F2").Select
Selection.NumberFormat = "d/m/yy;@"
Range("G3").Select
Selection.NumberFormat = "d/m;@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B9:B10").Select
Selection.NumberFormat = "d/m/yy;@"
Columns("D").ColumnWidth = 2.29
Range("C29:C30").Select
Selection.Style = "Currency"
Range("F18:F35").Select
Selection.Style = "Currency"
Windows("ed fact 2008-12.xls").Activate
Range("F3").Select
Windows("factures_mars_09.xls").Activate
Range("D1").Select
Selection.ClearContents
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("E8:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B13:F35").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Range("E15:F34,C29:C30").Select
Range("C29").Activate
Selection.Style = "Currency"
Selection.Style = "Currency"
Selection.NumberFormat = "#,##0.00 €"
End With
With Range("B9:B10").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Range("B11").Select
Selection.NumberFormat = "General"
Range("E3").Select
ActiveCell.FormulaR1C1 = "FACTURE N°"
Range("F3").Select
End With

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

With Range("B11").Select
Selection.NumberFormat = "General"" nuitée(s)"""
Range("G3").Select
Selection.ClearContents
Range("F2").Select
End With

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Columns("B:B").ColumnWidth = 36.29
Columns("B:B").ColumnWidth = 38.14
Columns("B:B").ColumnWidth = 41.71
Columns("B:B").ColumnWidth = 40.71
Range("B9:B11").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("C5").Select
Columns("F:F").ColumnWidth = 15.29
Columns("A:A").ColumnWidth = 8.43
End With

With Range("E8:F11").Select
Selection.Font.Bold = True
Range("A9:B11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1:B7").Select
Selection.Font.Bold = True
Range("E2:F3").Select
Selection.Font.Bold = True
Cells.Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("B4").Select
Columns("B:B").ColumnWidth = 44
End With


With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32.86
Range("B27").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B5").Select
Columns("B:B").ColumnWidth = 32
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=54).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B27").Select
ActiveCell.FormulaR1C1 = _
"remise exeptionnelle (sur hebergement) :"
With ActiveCell.Characters(Start:=1, Length:=61).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("E8:F11").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

With Range("B1").Select
ActiveSheet.Name = Range("f3").Value


With Windows("factures_mars_09.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close

Windows("ed fact 2008-12.xls").Activate
Range("B8").Select
Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select
End With


With Sheets("SAISIE DES INFORMATIONS").Select
Range("C2:E2").Select

Sheets("apercu de la facture").Range("f3").Value = Sheets("apercu de la facture").Range("f3").Value + 1

'et remetre à zero la feulle de saisie'
Range("C5").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
ActiveCell.FormulaR1C1 = ""
Range("C3").Select
ActiveCell.FormulaR1C1 = ""
Range("C4").Select
ActiveCell.FormulaR1C1 = ""
Range("D9").Select
ActiveCell.FormulaR1C1 = ""
Range("E9").Select
ActiveCell.FormulaR1C1 = ""
Range("F9").Select
ActiveCell.FormulaR1C1 = ""
Range("G9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("I9").Select
ActiveCell.FormulaR1C1 = ""
Range("J9").Select
ActiveCell.FormulaR1C1 = ""
Range("H18").Select
ActiveCell.FormulaR1C1 = ""
Range("G18").Select
ActiveCell.FormulaR1C1 = ""
Range("E18").Select
ActiveCell.FormulaR1C1 = ""
Range("D17").Select
ActiveCell.FormulaR1C1 = ""
Range("j16").Select
ActiveCell.FormulaR1C1 = ""
Range("C2").Select
Range("D10:I10").Select
Selection.ClearContents
Range("a1").Select
End With

Sheets("APERCU DE LA FACTURE").Visible = False
Sheets("A5").Visible = False
Application.ScreenUpdating = True
ActiveSheet.Protect ("mdp") 'deproteger la feuille'
End With

End Sub

ph.vanne · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

vous etes taquin!!
mais vous avez raison ....j'osais même pas vous la balancer mon usine a gaz..(mais j'y tiens beaucoup!!!)
mais pourquoi cet 'empillement' de données mal foutu se plante ...QUE de temps en temps ? sans message d'erreur?
y'a t il une piste a explorer plutot qu'une autre?

keepcool183 · 12 Mars 2009

Re : macro qui plante ..que de temps en temps!

Re ph.vanne,

Une suggestion met un exemple de ton fichier avec ce que tu veux faire parce que c'est pas motivant de se pencher sur un code cru.

A+

Effectuez une recherche sur Excel Downloads...

macro qui plante ..que de temps en temps!

ph.vanne

XLDnaute Junior

jetted

XLDnaute Occasionnel

Compte Supprimé 979

Guest

ph.vanne

XLDnaute Junior

tora

XLDnaute Occasionnel

keepcool183

XLDnaute Occasionnel

vbacrumble

XLDnaute Accro

keepcool183

XLDnaute Occasionnel

ph.vanne

XLDnaute Junior

keepcool183

XLDnaute Occasionnel

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums