Astuces Excel
  1. Astuces Excel
  3. Cycle erroné dans le contrôle macro en excel
Intereting Posts
Lire et écrire à partir de / vers le registre dans VBA Comment puis-je copyr des cellules spécifiques de la feuille 1 et coller dans les lignes correspondantes de la feuille 2, en fonction des valeurs des cellules de la feuille 2? Comment raccourcir le code de décalage VBA Création d'un calendar Pop-Up dans un projet Excel VSTO IsNumeric function renvoie true pour une cellule vide C # Excel.Interop Détection d'une action de sorting Comment afficher la signature dans la macro utilisée pour envoyer un courrier à partir de Outlook? Type Mismatch avec Excel VBA script destiné à autoupdate lorsque l'user change de cellule Obtention de la valeur du noeud XML lorsque l'espace de noms est présent à l'aide de VBA Android sauvegardez la sortie logcat dans cmd à html pour les utiliser dans Excel Transposer les valeurs d'une ligne où les critères sont respectés Comment puis-je créer des emails uniquement pour les adresses uniques d'une colonne dans Excel? Code pour copyr et coller une gamme à l'autre, plusieurs fois dans VBA excel Comment puis-je append plusieurs nombres dans une cellule tout en vérifiant sa condition connexe? Python fusionne les cellules 'n' dans Excel en fonction de l'état

Cycle erroné dans le contrôle macro en excel

J'ai cette macro 

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim x As Integer Dim S1 As Ssortingng Dim S2 As Ssortingng S1 = "Football" S2 = "Basket" x = 1 Do If IsEmpty(Cells(x, 5)) And ((Cells(x, 3) = S1) Or (Cells(x, 3) = S2)) Then MsgBox "Insert a value in the empty cell" Cancel = True End If x = x + 1 Loop Until Cells(x, 1) = "" End Sub

Lorsque je clique sur le button "x" pour fermer la feuille si la colonne 5 est vide et que la colonne 3 contient Football ou Basket la macro effectue un contrôle et apparaît une boîte de message pour avertir que vous avez inséré une valeur. La vérification va mais je ne sais pas que MsgBox apparaît 16 fois et non 1. Pourquoi?

Mettre mes commentaires dans une réponse. Ajoutant plus de choses également.

  1. Déclarez vos variables / objects. Vous serez less sujettes aux erreurs. Si vous travaillez avec des lignes dans Excel, il est préférable de les déclarer LONG
  2. Entièrement qualifié vos objects. Par exemple, quelles cellules et quelle feuille? Si vous souhaitez vérifier les cellules dans sheet1 mais sheet2 est active lorsque vous fermez le classur, vous n'obtiendrez pas les résultats souhaités
  3. Vous recevez de multiples messages car la boucle continue jusqu'à ce qu'elle trouve toutes les correspondances. Quittez la boucle après le premier match
  4. Faites que votre message dans MsgBox plus significatif. Comment l'user sait quelle cellule est vide 🙂

Est-ce ce que vous essayez? ( NON TESTÉ ) 

 Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Private Sub Workbook_BeforeClose (Annuler comme boolean) Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Dim S1 As Ssortingng, S2 comme string Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Dim lRow As Long, i As Long Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Dim ws As Worksheet Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Définir ws = ThisWorkbook.Sheets ("Sheet1") Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub S1 = "Football": S2 = "Panier" Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Avec ws Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub '~~> Trouver la dernière ligne qui contient des données Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub '~ ~> on bouclera jusqu'à présent Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub lRow = .Range ("A" et .Rows.Count) .End (xlUp) .Row Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Pour i = 1 à lRow Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Si Len (Trim (.Range ("E" & i) .Value)) = 0 Then Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Sélectionnez Case .Range ("C" & i) .Value Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Cas S1, S2 Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub '~~> Dites à l'user quelle cellule est vide Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub MsgBox "Insérer une valeur dans la cellule" & _ Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub .Range ("E" & i). Adresse Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Annuler = Vrai Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub '~~> Quittez la boucle après le premier match Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Sortie pour Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub Fin Choisir Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim S1 As Ssortingng, S2 As Ssortingng Dim lRow As Long, i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") S1 = "Football": S2 = "Basket" With ws '~~> Find the last row which has data '~~> we will loop till there lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 1 To lRow If Len(Trim(.Range("E" & i).Value)) = 0 Then Select Case .Range("C" & i).Value Case S1, S2 '~~> Tell user which cell is empty MsgBox "Insert a value in the cell " & _ .Range("E" & i).Address Cancel = True '~~> Exit the loop after the first match Exit For End Select End If Next i End With End Sub