Option Explicit
' -------------------------------------------------------------
' Essai 1 :  modification des valeurs de cellules dans Excel
' -------------------------------------------------------------
Sub EssaiRange1()
' Déclaration d’une variable Zone
Dim Zone As Range
' Initialisation de variable Zone
Set Zone = Range("A1:F2")
' Modification des propriétés de la variable Zone
Zone = 2
End Sub

' ----------------------------------------------------------------
' Essai 2 :  modification des propriétés de cellules dans Excel
' ----------------------------------------------------------------
Sub EssaiRange2()
' Déclaration d’une variable Zone
Dim Zone As Range
' Initialisation de variable Zone
Set Zone = Range("A1:F2")
' Modification des propriétés de la variable Zone
Zone.Value = 2
Zone.Font.Bold = True
Zone.Interior.ColorIndex = 6
End Sub
' ----------------------------------------------------------------------------
' Essai 3 :  modification des propriétés de cellules dans deux feuilles Excel
' ---------------------------------------------------------------------------
Sub EssaiRange()
' Déclaration d’une variable Zone
Dim Zone As Range
' Initialisation de variable Zone
Worksheets("Feuil1").Activate
Set Zone = Range("A1:F2")
' Modification des propriétés de la variable Zone
Zone.Value = 2
Zone.Font.Bold = True
Zone.Interior.ColorIndex = 6

' Worksheets(2).Activate
Worksheets("Feuil2").Activate
Set Zone = Range("A1:B5")
Zone.Value = 4
Zone.Font.Bold = True
Zone.Interior.ColorIndex = 43

End Sub

' ----------------------------------------------------------------------------
' EssaiRange4 :  utilisation des fonctions d’Excel dans un code VBA
' ----------------------------------------------------------------------------
Sub EssaiRange4()
' Déclaration d’une variable Zone
Dim Zone As Range, Zone2 As Range
Dim Nb As Long
Dim Somme As Double
' Initialisation de variable Zone
Worksheets("Feuil2").Activate
Set Zone = Range("A1:B5")
Zone.Value = 4
Zone.Font.Bold = True
Zone.Interior.ColorIndex = 43

' Appel de fonctions Excel
Set Zone2 = Range("B1:B5")
Nb    = Application.WorksheetFunction.CountA(Zone2)
Somme = Application.WorksheetFunction.Sum(Zone2)

MsgBox "Somme sur " & Nb & "éléments : " & Somme

End Sub

' ----------------------------------------------------------------------------
' CopierLigne :  manipulation of Excel cells  in VBA code
' Look for a cell of the A column (equal to 3)
' and copy the corresponding whole line in the sheet « Feuil2 »
' ----------------------------------------------------------------------------
Sub CopierLigne()
 Dim Cell as Range
 For Each Cell In Sheets(1).Range(“A:A")
      If Cell.Value = “3" Then
          Cell.EntireRow.copy Destination:=Sheets(“Feuil2").range(“A" & cell.row)
          ‘You might want to exit here if there's only one value to find
          Exit For
      End If
 Next


' ----------------------------------------------------------------------------
' Copy Excel comments from column N(=3) to column N+1
' ----------------------------------------------------------------------------
Public Sub CopyComments()
Dim Cmt As Comment
Dim Ligne As Integer, Col As Integer
' Go through all the cells in Column Col (or active column),
' and check to see if the cell has a comment.
Col = 3   'Check with ActiveCell.column?
For Ligne = 1 To WorksheetFunction.CountA(Columns(Col))
  Set Cmt = Cells(Ligne, Col).Comment
  If Not (Cmt Is Nothing) Then  'If there is a comment,
  'paste its text into column Col+1 and delete the original comment.
     Cells(Ligne, Col + 1).Value = Cells(Ligne, Col).Comment.Text
  End If
Next
End Sub

' ----------------------------------------------------------------------------
' Transfer Excel comments from column N(=3) to column N+1
' ----------------------------------------------------------------------------
Public Sub TransferComments()
Dim Cmt As Comment
Dim Ligne As Integer, Col As Integer
' Go through all the cells in Column Col (or active column),
' and check to see if the cell has a comment.
Col = 3   'Check with ActiveCell.column?
For Ligne = 1 To WorksheetFunction.CountA(Columns(Col))
  Set Cmt = Cells(Ligne, Col).Comment
  If Not (Cmt Is Nothing) Then
  'If there is a comment, paste the comment text into column Col+1 and delete the original comment.
     If (Cells(Ligne, Col + 1).Comment Is Nothing) Then
        With Cells(Ligne, Col + 1).AddComment
          .Visible = True
          .Text Cmt.Text   '.Text Cells(Ligne, Col).Comment.Text
        End With
     End If
  End If
Next
End Sub