Sub ImportPage(Feuille As String, Lien As String) Static Tentatives As Integer On Error GoTo Erreur
Exit Sub Erreur: Tentatives = Tentatives + 1 If Tentatives < 5 Then ImportPage Feuille, Lien 'on s'essaie durant quelques coups Else Tentatives = 0 ' ici ça va sortir End If
Set Cel = .Columns("A").Find(what:=LaDate, LookIn:=xlValues, lookat:=xlPart) If Not Cel Is Nothing Then Depart = Cel.Address Do .Rows(Cel.Row + 1 & ":" & Cel.Row + 9).ClearContents Set Cel = .Columns("A").FindNext(Cel) Loop While Not Cel Is Nothing And Depart <> Cel.Address End If
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub Nettoyage() Dim I As Long, J As Long Dim Cel As Range Dim Adresse As String Dim Depart As Long, Fin As Long Dim LgDep As Long Dim LgFin As Long Dim Lgder As Long Dim Ligne As Long Dim LocalDate As String Dim Separateur As String Dim Ws As Worksheet Application.ScreenUpdating = False Set Ws = Sheets("Import") With Ws ' On supprime les lignes jusqu'à la 1ère occurence de la date LgFin = .Cells(Rows.Count, "A").End(xlUp).Row For I = 1 To LgFin If InStr(1, .Range("A" & I), LaDate) > 0 Then .Rows(1 & ":" & I - 1).Delete 'Effacer tout d'un coup plutôt que ligne par ligne Exit For End If Next ' On cherche la ligne qui est juste après le dernier tableau ' Et on efface de cette ligne jusqu'à la fin de la page LgFin = .Cells(Rows.Count, "A").End(xlUp).Row Set Cel = .Columns("A").Find(what:="La base numéro 1 du Turf", LookIn:=xlValues, lookat:=xlWhole) If Not Cel Is Nothing Then .Rows(Cel.Row & ":" & LgFin).ClearContents Else MsgBox "Impossible de trouver le marqueur : La base numéro 1 du Turf" Exit Sub 'End End If ' Entre chaque titre des réunions et le tableau on efface les lignes For I = 1 To LgFin If InStr(1, .Range("A" & I), LaDate) > 0 Then For J = I To LgFin If UCase(.Range("A" & J)) = "N" Then .Rows(I + 1 & ":" & J - 1).Delete Exit For End If Next End If Next ' On efface toutes les lignes avec "fermer" Set Cel = .Columns("A").Find(what:="fermer", LookIn:=xlValues, lookat:=xlWhole) If Not Cel Is Nothing Then Adresse = Cel.Address Do .Rows(Cel.Row).ClearContents Set Cel = .Columns("A").FindNext(Cel) Loop While Not Cel Is Nothing End If ' On supprime toutes les lignes vierges On Error Resume Next .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End With Set Ws = Nothing End Sub
Sub LesReunions() Dim Feuille As String Dim J As Long Dim Lgder As Long Dim WsImp As Worksheet Dim Cel As Range Dim Progression As Double Dim Pas As Double Set WsImp = Sheets("ImportReunions") Set Ws = Sheets("Import") Lgder = Ws.Range("A" & Rows.Count).End(xlUp).Row Pas = (UserForm1.Label5.Width - 4) / NbTablo For J = 1 To Lgder If InStr(1, Ws.Range("A" & J), LaDate) > 0 Then Feuille = Left(Ws.Range("A" & J), InStr(1, Ws.Range("A" & J), " -") - 1) Else If Ws.Range("B" & J).Hyperlinks.Count 1 And LCase(Ws.Range("H" & J)) "x" Then Progression = Progression + Round((100 / NbTablo), 2) UserForm1.Label2.Caption = Val(UserForm1.Label2.Caption) + 1 UserForm1.Label3.Caption = Progression & "%" UserForm1.Label4.Width = Val(UserForm1.Label2.Caption) * Pas UserForm1.Caption = Ws.Range("B" & J).Hyperlinks(1).Address UserForm1.Repaint ImportPage "ImportReunions", Ws.Range("B" & J).Hyperlinks(1).Address With WsImp Set Cel = .Columns("A").Find(what:="Origines", LookIn:=xlValues, lookat:=xlWhole) If Not Cel Is Nothing Then .Rows(Cel.Row & ":" & Rows.Count).Delete Else MsgBox "Impossible de trouver le marqueur : Origines" Exit Sub 'End End If Set Cel = .Columns("A").Find(what:="1er", LookIn:=xlValues, lookat:=xlWhole) If Not Cel Is Nothing Then .Rows("1:" & Cel.Row - 2).Delete Else MsgBox "Impossible de trouver le marqueur : 1er" Exit Sub 'End End If On Error Resume Next .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 Lgder = .Range("A" & Rows.Count).End(xlUp).Row If FeuilleExiste(Feuille) = False Then Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = Feuille End If With .Range("A1:N" & Lgder) .Borders.Weight = xlThin .Copy Destination:=Sheets(Feuille).Range("A" & Rows.Count).End(xlUp).Offset(2, 0) End With End With End If End If Next J Set WsImp = Nothing Set Ws = Nothing End Sub
Private Sub CommandButton1_Click() UserForm1.Show vbModeless End Sub Private Sub CommandButton2_Click() Unload UserForm1 End Sub
Option Explicit 'Pour enlever la barre de titre du UF Public Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Const GWL_STYLE = (-16) Const WS_CAPTION = &HC00000 Const SWP_FRAMECHANGED = &H20 Public Declare Function FindWindowA Lib "user32" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Public Declare Function GetWindowRect Lib "user32" _ (ByVal hwnd As Long, lpRect As RECT) As Long Public Declare Function GetWindowLong Lib "user32" Alias _ "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long Public Declare Function SetWindowLong Lib "user32" Alias _ "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, _ ByVal dwNewLong As Long) As Long Public Declare Function SetWindowPos Lib "user32" _ (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, _ ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, _ ByVal wFlags As Long) As Long Public Type POINTAPI X As Long Y As Long End Type Public m_CursorPos As POINTAPI Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long Public Declare Function WindowFromPoint Lib "user32" (ByVal xPoint As Long, ByVal yPoint As Long) As Long Public Afficher As Boolean Sub OteTitleBarre(stCaption As String, pbVisible As Boolean) Dim vrWin As RECT Dim style As Long Dim lHwnd As Long '- Recherche du handle de la fenêtre par son Caption lHwnd = FindWindowA(vbNullString, stCaption) If lHwnd = 0 Then MsgBox "Handle de " & stCaption & " Introuvable", vbCritical Exit Sub End If GetWindowRect lHwnd, vrWin style = GetWindowLong(lHwnd, GWL_STYLE) If pbVisible Then SetWindowLong lHwnd, GWL_STYLE, style Or WS_CAPTION Else SetWindowLong lHwnd, GWL_STYLE, style And Not WS_CAPTION End If SetWindowPos lHwnd, 0, vrWin.Left, vrWin.Top, vrWin.Right - vrWin.Left, _ vrWin.Bottom - vrWin.Top, SWP_FRAMECHANGED End Sub
Option Explicit Dim LG3 As Integer Dim Deb As Integer Private Sub UserForm_Initialize() OteTitleBarre Me.Caption, False Me.Height = 43 End Sub
quand je le met dans mon fichier avec les importation cela ne fonctionne pas