Vous trouverez dans cette rubrique des exemples concernant des programmes s'appliquant aux feuilles de calcul.
Comparer deux colonnes. Effectuer un calcul sur une plage (Ex. Multiplier par 2 les cellules de la plage). Effectuer une recopie incrémentée. Insérer une ligne vide toutes les 2 lignes (ou plus). Introduire une variable dans une formule SOMME (Ex. Un N° de ligne). Introduire une variable dans une formule SOMME (Ex. l'adresse d'une cellule). Mettre en évidence les cellules répondant à un critère. Recherche du nombre d'occurence. Rechercher la dernière cellule d'une plage. Recopier une plage variable vers une autre feuille du classeur. Récupérer la colonne active sous forme littérale (A, AB, AZ, etc...). Récupérer la somme d'une colonne dans une variable. Récupérer le nombre de colonnes. Récupérer le nombre de lignes. Récupérer la somme du produit de 2 colonnes (Somme des N (A*B)). Répertoire et nom de fichier dans pied de page. Supprimer les doublons dans une colonne. Supprimer les lignes vides d'une plage. Supprimer un nom dans une feuille de calcul. Supprimer tous les noms dans une feuille de calcul. Supprimer les noms avec un joker dans une feuille de calcul. Supprimer les lignes contenant du texte. Utiliser des macro-commandes dans une feuille protégée.
Permet d'ajouter dans la colonne 1 les valeurs qui seraient dans la colonne 2 et pas dans la colonne 1.
Sub CompareTwoColonnes()
Dim Cell As Range, Plage As Range, I As Long
Set Plage = Range("A1", [A1].End(xlDown))
I = Plage.Count
Application.ScreenUpdating = False
For Each Cell In Range("B1", [B1].End(xlDown))
If Plage.Find(Cell, Plage(1), xlValues, xlWhole) Is Nothing Then
I = I + 1
Cells(I, 1) = Cell
End If
Next Cell
End Sub
Permet de multiplier l'ensemble d'une plage par une valeur (2 dans l'exemple)
Sub MultiplieParDeux()
' Utilisation d'un tableau intermédiaire
Dim Tabl
Dim I As Integer, J As Integer
Tabl = Range("A1:C1000").Value
For I = 1 To 1000
For J = 1 To 3
  Tabl(I, J) = Tabl(I, J) * 2
Next J
Next I
Application.ScreenUpdating = False
Range("A1:C1000").Value = Tabl
End Sub
Permet d'effectuer une recopie incrémentée à partir de A1 et A2
Sub RecopieIncrementee()
Set PlageSource = Worksheets("Feuil1").Range("A1:A2")
' Effectue une recopie incrémentée de A1 à A20
Set PlageARemplir = Worksheets("Feuil1").Range("A1:A20")
PlageSource.AutoFill Destination:=PlageARemplir
End Sub
Permet d'insérer dans un tableau une ligne vide toutes les deux lignes (ou plus)
Sub MacroInsertUneLigneSurDeux()
Dim Line As Integer
Range("A2").Select
Line = 1
Recommence:
Line = Line + 2
Rows(Line).Select
Selection.Insert Shift:=xlDown
If Line < ActiveSheet.UsedRange.Rows.Count Then
GoTo Recommence
End If
End Sub
Sub VariableSomme()
' La colonne A est la colonne où l'on doit effectuer une somme
' Se placer sur la première ligne vide de la colonne pour poser la formule Somme
Range("A1").End(xlDown).Offset(1, 0).Select
' Récupération du nombre de lignes à comptabiliser dans la formule
MonNoDeLigne = -(ActiveCell.Row) + 1
ActiveCell.FormulaR1C1 = "=SUM(R[" & MonNoDeLigne & "]C:R[-1]C)"
End Sub
Ce programme va sélectionner toutes les cellules correspondant
à la valeur contenue dans D2.
Sub SelectCellulesValeurDeterminee()
LaValeur = Range("D2").Value
Range("A1").Select
For Each cll In ActiveCell.CurrentRegion
If cll.Value = LaValeur Then Plg = Plg & cll.Address() & ","
Next cll
If Len(Plg) > 0 Then Range(Left(Plg, Len(Plg) - 1)).Select
End Sub
Pour sélectionner les lignes à la place des cellules :
Remplacer la ligne de code
If cll.Value = LaValeur Then Plg = Plg & cll.Address() & ","
par
If cll.Value = LaValeur Then Plg = Plg & cll.Row() & ":" & cll.Row() & ","
Ce programme va compter le nombre de fois qu'il rencontre la valeur ValeurAChercher
Sub RechercheNbOccurence()
Résultat = Application.CountIf(Range("A:A"), "ValeurAChercher")
MsgBox ("Le texte ValeurAChercher est présent : " & Résultat & " fois.")
End Sub
Ce programme permet de positionner le curseur sur la dernière cellule d'une plage
correspondant à la cellule en cours.
Sub AllerADernierecellule()
'Sélectionne la plage en cours
ActiveSheet.UsedRange.Select
'Sélectionne la dernière cellule de la plage en cours
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(0, 0).Select
End Sub
Ce programme permet de recopier une plage variable A8 à Gx vers une autre feuille (Feuil2) en A1.
correspondant à la cellule en cours.
Sub SelectRecopie()
' Redéfinir la plage en fonction du nombre de lignes
' Sélectionner cette plage
' Copier les valeurs contenues dans cette plage sur la feuille 2 à la cellule "A1"
With Worksheets(1)
.Range(.[A1], .[G1].End(xlDown)).Copy Worksheets(2).[A1]
End With
End Sub
Ce programme permet de récupérer la lettre de la colonne de la cellule active.
Sub LetCol()
Dim Let_Col As String
Let_Col = Left(Mid(ActiveCell.Address, 2), _
Len(Mid(ActiveCell.Address, 2)) - (Len(Mid(Mid(ActiveCell.Address, 2), _
Application.Search("$", Mid(ActiveCell.Address, 2), 1) + 1)) + 1))
MsgBox Let_Col
End Sub
Autre variante avec utilisation des valeurs VRAI(-1) et FAUX (0)
Sub LettreColonne()
' Utilisation des valeurs VRAI (-1) et FAUX (0)
Lettre_Col = Left(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
MsgBox Lettre_Col
End Sub
Ce programme effectue la somme de la colonne A et la met dans une variable LaSomme.
Sub FaireSommeColonne()
LaSomme = Application.Sum(Range("A1").EntireColumn)
MsgBox LaSomme
End Sub
1 - Nombre de colonnes comprises entre la première cellule et la dernière cellule
de la plage sélectionnée (y compris éventuellement les colonnes vides)
Sub NombreDeColonnes()
NbColonnes=ActiveSheet.UsedRange.Columns.Count
MsgBox NbColonnes
End Sub
2 - Nombre de colonnes comprises entre la colonne A (vide ou pas) et
la dernière cellule utilisée (cette sélection peut contenir des colonnes vides).
Sub NombreDeColonnes()
NbColonnes = Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox NbColonnes
End Sub
1 - Nombre de lignes comprises entre la première cellule et la dernière cellule
de la plage sélectionnée (y compris éventuellement les lignes vides)
Sub NombreDeColonnes()
NbLignes=ActiveSheet.UsedRange.Rows.Count
MsgBox NbLignes
End Sub
2 - Nombre de lignes comprises entre la colonne A (vide ou pas) et
la dernière cellule utilisée (cette sélection peut contenir des lignes vides).
Sub NombreDeLignes()
NbLignes = Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox NbLignes
End Sub
Ce programme effectue le produit des valeurs de la colonne A par celles de la colonne B et additionne ces résultats.
Sub SommeProduit()
Cells(1, "A").Select
NbLignes = Cells(Range("A:A").Count, ActiveCell.Column).End(xlUp).Row
NbLignes = Cells(16, 1).End(xlUp).Row
TotalIntermediaire = 0
For Lgn = 2 To NbLignes 'Etiquettes en ligne 1
If Not Rows(NbLignes).Hidden Then TotalIntermediaire = TotalIntermediaire + Cells(Lgn, "A") * Cells(Lgn, "B")
End If
Next Lgn
MsgBox TotalIntermediaire
End Sub
Procédure à mettre dans This WorkBook
La mise a jour ce fait dès que l'on active un onglet.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ZtPath As String
ZtPath = Application.ActiveWorkbook.Path
ActiveSheet.PageSetup.LeftFooter = ZtPath & "\" & "&F"
End Sub
Ce programme efface les doublons dans la colonne sans supprimer les lignes.
Sub EffaceDoublonsColonnnes()
Const Cell_Départ As String = "A1"
Dim Fin As Range, I As Long, J As Long, Col As Integer
Dim ModeCalcul As Long
With Application
ModeCalcul = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Col = Range(Cell_Départ).Column
Set Fin = Range(Cell_Départ).End(xlDown)(2)
On Error Resume Next
Do
I = J + 1
J = Range(Cells(I, 1), Fin).ColumnDifferences(Cells(I, 1))(0).Row
If J > I Then Range(Cells(I + 1, 1), Cells(J, 1)).ClearContents
Loop Until Err
Application.Calculation = ModeCalcul
End Sub
Ce programme supprime les lignes vides dans une plage.
Sub DetruireLigne()
DerniereLigne = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = DerniereLigne To 1 Step -1
If Application.CountA(Rows(R)) = 0 Then Rows(R).Delete
Next R
End Sub
Ce programme supprime le nom "Base"" dans toutes les feuilles du classeur.
Sub SupprimeUnNom()
For Each Nm In ActiveWorkbook.Names
If Nm.Name Like "Base" Then
Nm.Delete
End If
Next Nm
End Sub
Ce programme supprime tous les noms dans le classeur.
Sub SupprimeNomsAvecJoker()
For Each Nm In ActiveWorkbook.Names
If Nm.Name Like "*Base" Then
Nm.Delete
End If
End Sub
Ce programme recherche dans la colonne A les cellules contenant du texte et supprime les lignes associées à ces cellules.
Sub SupprimeLignesAvecTtexte()
NbRw = Application.CountA(Columns("A:A"))
For Rw = NbRw To 1 Step -1
If Application.IsText(Cells(Rw, 1)) Then Rows(Rw).Delete
Next Rw
End Sub
Ce programme permet d'utiliser des macro-commandes dans une feuille protégée par Outils, Protection.
Worksheets("Feuil1").Protect UserInterfaceOnly:=True permet de manipuler par macro une feuille protégée, tout en laissant la protection active par rapport aux manipulations de l'utilisateur.
Private Sub Workbook_Open()
Worksheets("Feuil1").Protect UserInterfaceOnly:=True
End Sub
Instruction à mettre dans Workbook_Open, cette propriété n'étant pas
enregistrée avec le classeur.