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
| Sub Commentaires()
Dim Cell
Dim SDA, NOM, EMPL, PRIS As String
Dim Rep As Integer
For Each Cell In ActiveSheet.UsedRange
ActiveSheet.Range("B1").Select
If Cell.HasFormula Then
With Cell
On Error Resume Next
' Recherche le numéro de ligne contenant la valeur dans la colonne "B"
Rep = Sheets("Annuaire").Range("B:B").Find(What:=.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
EMPL = Sheets("Annuaire").Cells(Rep, 1).Value
SDA = Sheets("Annuaire").Cells(Rep, 3).Value
NOM = Sheets("Annuaire").Cells(Rep, 4).Value
PRIS = Sheets("Annuaire").Cells(Rep, 5).Value
' Inscrit le commentaire
.Comment.Delete
.AddComment
.Comment.Text Text:="EMPLACEMENT: " & EMPL & Chr(10) & "N° INTERNE " & .Value & Chr(10) & "SDA: " & SDA & Chr(10) & "NOM: " & NOM & Chr(10) & "PRISE: " & PRIS
.Comment.Shape.TextFrame.AutoSize = True
If .Comment.Text = "" Then .Comment.Delete
End With
End If
Next
End Sub |
Partager