Re : Inputbox
Bonjour fred
j abuse un peu de ta gentillesse. Apres plusieurs essais avec ton code, il doit manquer un petit quelque chose. En faite, mon besoin c'est que je veux que l'utilisateur reçoive un message d'erreur de type "ptf introuvable" quand il saisi un code ptf qui n'est pas dans la base. Et aussi quand je fais annuler dans le INPUTBOX que le programme s'arrete.
Merci encore une fois : MOT DE PASSE du fichier c'est octogone
Sub Hors_Bilan()
Application.ScreenUpdating = False
Dim Num_ptf As Variant
Num_ptf = Application.InputBox("Entrez le numéro de PTF", "Code PTF")
Dim i As Double
Dim j As Integer
Dim Montableau(50000, 8) As Variant
Dim DerniereLigne As Integer
Dim sh1 As Worksheet ' Onglet variable
Dim l As Long ' pour supprimer les lignes vides
i = 1
j = 0
Workbooks.Open Filename:="C:\Users\H\Documents\_Macro hors-bilan OBLIG MONDE\HISINV GP14 30.09.2011.xls"
Set sh1 = Worksheets(1)
While Range("D" & i).Value <> ""
If Num_ptf = CStr(Range("D" & i).Value) Then
j = j + 1
i = i + 1
ElseIf Num_ptf <> CStr(Range("D" & i).Value) Then
MsgBox ("ptf introuvable")
If Range("F" & i).Value = "FUTU" Or Range("F" & i) = "OPTI" Then
CAT = Range("F" & i).Value
ISIN = Range("G" & i).Value
LIBELLE = Range("I" & i).Value
DEVISE = Range("J" & i).Value
QUANTITE = Range("Z" & i).Value
COURS = Range("AC" & i).Value
NUMERO_PTF = Range("D" & i).Value
Montableau(j, 0) = CAT
Montableau(j, 1) = ISIN
Montableau(j, 2) = LIBELLE
Montableau(j, 4) = DEVISE
Montableau(j, 6) = QUANTITE
Montableau(j, 7) = COURS
Montableau(j, 8) = NUMERO_PTF
End If
End If
i = i + 1
j = j + 1
Wend
If j > 0 Then
DerniereLigne = j - 1 ' pour pas aller trop loin dans la prochaine boucle!
ActiveWorkbook.Worksheets.Add
Sheets("feuil1").Select 'active la feuil1 pour y mettre Montableau
For i = 0 To DerniereLigne 'UBound(Montableau, 2)
For j = 0 To UBound(Montableau, 2) 'UBound(Montableau, 1) il fallait mettre 2 au lieu de 1 car
Cells(i + 3, j + 1) = Montableau(i, j)
Next j
Next i
End If
Range("A2").Select
ActiveCell.FormulaR1C1 = "CAT"
Range("B2").Select
ActiveCell.FormulaR1C1 = "ISIN"
Range("C2").Select
ActiveCell.FormulaR1C1 = "LIBELLE"
Range("D2").Select
ActiveCell.FormulaR1C1 = "POSITIONS"
Range("E2").Select
ActiveCell.FormulaR1C1 = "DEV"
Range("F2").Select
ActiveCell.FormulaR1C1 = "CHANGE"
Range("G2").Select
ActiveCell.FormulaR1C1 = "QUANTITE"
Range("H2").Select
ActiveCell.FormulaR1C1 = "COURS"
Range("I2").Select
ActiveCell.FormulaR1C1 = "NOMINAL"
Range("J2").Select
ActiveCell.FormulaR1C1 = "VB Hors bilan"
Range("K2").Select
ActiveCell.FormulaR1C1 = "VB Absolue Hors bilan"
Range("L2").Select
ActiveCell.FormulaR1C1 = "§ 3.1.4"
Range("M2").Select
ActiveCell.FormulaR1C1 = "§ 3.3"
Range("A6000").End(xlUp).Select 'supprime les lignes vides
Do
If IsEmpty(ActiveCell) Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell.Row = 1
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("2:2").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A:A,B:B,C:C,D
,E:E,G:G,H:H").Select
Range("E1").Activate
Selection.Copy
Windows("Calcul hb new.xls").Activate
Sheets("Calcul_Engagement_HB").Select
Range("A1").Select
ActiveSheet.Paste
'intégration formules de calculs
Range("D3").Select
While ActiveCell.Offset(0, -1) <> ""
ActiveCell.FormulaR1C1 = "=IF(RC[2]<0,""Couverture"",""Autres opérations"")"
ActiveCell.Offset(1, 0).Select
Wend
'Copies 2pour faciliter recherchev
Workbooks.Open Filename:= _
"C:\Users\H\Desktop\macro HB\FUTURES ET OPTIONS 30.09.2011BIS.xls"
Sheets("FUTURES").Select
Cells.Select
Selection.Copy
Windows("Calcul hb new.xls").Activate
Sheets("FUTURES").Select
Range("A1").Select
ActiveSheet.Paste
Windows("FUTURES ET OPTIONS 30.09.2011BIS.xls").Activate
Sheets("DELTA OPTIONS").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Calcul hb new.xls").Activate
Sheets("OPTIONS").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("futures").Select
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
'filtre TRES pour récupérer les devises
Windows("HISINV GP14 30.09.2011.xls").Activate
Sheets(Sheets.Count).Select 'selectionne le dernier onglet
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="TRES"
Range("M72").Select
Columns("A:I").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="TRES"
Range("A3:AK179").Select
Selection.Copy
Windows("Calcul hb new.xls").Activate
Sheets("DEVISES").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("T1").Select
Range("U1").Select
ActiveCell.FormulaR1C1 = "=+RC[2]/RC[-4]"
Range("U1").Select
Selection.AutoFill Destination:=Range("U1:U7")
Range("U1:U7").Select
Selection.AutoFill Destination:=Range("U1:U41"), Type:=xlFillDefault
Range("U1:U41").Select
ActiveWindow.SmallScroll Down:=-30
Range("U2").Select
Columns("U:U").EntireColumn.AutoFit
Columns("U:U").Select
Selection.NumberFormat = "0.0000000000000" '14 zéros
'recherches V nominal taux et maturité
Sheets("Calcul_Engagement_HB").Select
Range("A2").Select
Selection.End(xlDown).Select
Nbligne = Selection.Row
Range("J2").Select
For Vl = 3 To Nbligne
'J
Range("J" & Vl).Value = "=VLOOKUP(RC[-8],FUTURES!R1C1:R265C4,4,FALSE)"
'K
Range("K" & Vl).Value = "=VLOOKUP(RC[-6],DEVISES!R1C1:R32C28,21,FALSE)"
'L
Range("L" & Vl).Value = "=VLOOKUP(RC[-10],FUTURES!R1C1:R255C10,3,FALSE)"
'M
Range("M" & Vl).Value = "=VLOOKUP(RC[-11],FUTURES!R1C1:R255C10,9,FALSE)"
Next
'intégration formules de calculs
Sheets("Calcul_Engagement_HB").Select
Range("H3").Select
While ActiveCell.Offset(0, -1) <> ""
ActiveCell.FormulaR1C1 = "=+RC[-2]*RC[-1]*RC[2]*RC[3]"
ActiveCell.Offset(1, 0).Select
Wend
Range("I3").Select
While ActiveCell.Offset(0, -4) <> ""
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,RC[-1]*-1,RC[-1])"
ActiveCell.Offset(1, 0).Select
Wend
Range("I3").Select
While ActiveCell.Offset(0, -4) <> ""
Selection.Style = "Comma"
ActiveCell.Offset(1, 0).Select
Wend
Range("H3").Select
While ActiveCell.Offset(0, -1) <> ""
Selection.Style = "Comma"
ActiveCell.Offset(1, 0).Select
Wend
'Annule toutes les alertes Excel
Application.DisplayAlerts = False
Windows("HISINV gp14 30.09.2011.xls").Activate
ActiveWindow.Close
Windows("FUTURES ET OPTIONS 30.09.2011BIS.xls").Activate
ActiveWindow.Close
'Restaure l'affichage des Alertes
Application.DisplayAlerts = True
'Mise en forme tableau
Range("A2:M2").Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
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
Application.ScreenUpdating = True
MsgBox ("traietment terminé")
End Sub