1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
|
Sub Test()
Dim Plage As Range, InpRng As Range
Dim Cel As Range
On Error GoTo Err_Read
Set InpRng = ThisWorkbook.Worksheets("Feuil1").Range("A2:A150").SpecialCells(xlCellTypeConstants)
Debug.Print InpRng.Address, InpRng.Cells.Count
For Each Cel In InpRng
If Not (IsEmpty(Cel)) Then
Application.DisplayAlerts = False
Workbooks.Open CStr((Cel.Value))
Application.DisplayAlerts = True
Call Histo(ThisWorkbook)
Else: MsgBox Cel.Address & " invalide"
End If
Next Cel
Err_Read:
If Err.Number <> 0 Then
Errmsg = "Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & Cel.Address
MsgBox Errmsg, vbCritical
Err.Clear
Exit Sub
End If
End Sub
Sub Histo(Wb As Workbook)
Dim Plage As Range
Dim Cel As Range
Dim Sh As Worksheet
For Each Sh In Wb.Sheets
If Left(Sh.Name, 5) = "Valor" Then
Set Plage = Sh.Range(Sh.Cells(2, 1), Sh.Cells(Sh.Rows.Count, 1).End(xlUp))
For Each Cel In Plage
'Condition 1) Si colonne A, B, non vide et si colonne F est en % alors copier E dans G
If Cel.Value <> "" _
And Cel.Offset(0, 1).Value <> "" _
And Cel.Offset(0, 5).NumberFormat = "0.00%" Then
Cel.Offset(, 10).Value = Cel.Offset(, 6).Value
'Condition 2 ) Si colonne A, B, non vide, et si colonne F n'est pas en % alors copier F dans G
ElseIf Cel.Value <> "" _
And Cel.Offset(0, 1).Value <> "" _
And Cel.Offset(0, 5).NumberFormat <> "0%" Then
Cel.Offset(, 10).Value = Cel.Offset(, 5).Value
End If
Next Cel
End If
Next Sh
Set Plage = Nothing
ActiveWb.Save
ActiveWb.Close
End Sub |
Partager