Discussion :

[SFLEY]

Bonjour,
J'aimerais transformé cette formule :

=RECHERCHEV(CONCATENER(L(2)C;CONCATENER("-";DROITE(L(1)C;2)));annuaire;2;0)

en script VBA

la formule permet de concatener les deux cellules sous elle meme puis d'aller chercher dans un onglet annuaire la valeur de la seconde colonne de cette onglet en fonction du resultat de la concatenation.

ex:

ma formule est ici "formule"
la cellule N-1 est "N01"
la cellule N-2 est "0-00"

la concatenation donne la valeur "0-00-01"

comme le tableau "annuaire" contient :

cellule 1 cellule 2
0-00-01 4546

la fonction me retourne 4546

merci de votre aide

[conconbrr]

Salut,

Pour concaténer utilise simplement l'opérateur "&"
Je pense que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("Cellule1").value & range("Cellule2").value

devrait suffire.

En ce qui concerne la recherche utilses l'opérateur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.find

ça marche très bien et c'est très rapide.
Un petit tour du côté de l'aide et tu devrais tout comprendre, sinon n'hésites pas on t'aideras

[SFLEY]

merci

je débute en VBA alors dans la ligne de code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("Cellule1").value & range("Cellule2").value

comment peut on définir Cellule 1 et cellule 2

comme étant les cellules sous la position de la cellule d'origine ?

-------
cellule
-------
cellule1
-------
cellule2
-------

[conconbrr]

Utilises la fonction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.offset

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

worksheets("Feuil1").range("Cellule").offset(i,j).value

Offset permet de décaler de i lignes et de j colonnes

[SFLEY]

J'ai un peu changé mon fusil d'épaule je suis donc arrivé au code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
Option Explicit
 
Sub Commentaires()
 
Dim Cell
 
For Each Cell In ActiveSheet.UsedRange
    If Cell.HasFormula Then
 
        With Cell
 
            On Error Resume Next
 
       If InStr(Worksheets("annuaire").Value, Cell.Value) <> 0 Then
 
   .Comment.Delete
   .AddComment
   .Comment.Text Text:=Cell.Value & Chr(10) & "Nom" & "Prénom" & Chr(10) & "Adresse m@il"
   .Comment.Shape.TextFrame.AutoSize = True
 
 
  Else
    MsgBox "valeur non trouvée"
End If
 
 
 
        End With
    End If
Next
End Sub

Avec ce bout de code, je cré un commentaires dans les cellulles où il y a des formules et si la valeur de la cellule est contenu dans la feuille Annuaire j'affiche cette valeur dans le commentaire.

Mais je voudrais ajouter dans mon commentaire les champs nom prenom et adresse qui sont les valeurs des cellules à coté de la valeur trouvé dans la feuille annuaire.

EX :

Si la Cellule 12 de la feuille 1 à pour valeur 3022

et que La feuille Annuaire possède une ligne:

3022|dupont|jean|jdupont@babacool.fr|

Je voudrais que le commentaire de la Cellule 12 de la feuille 1 soit :

3022
dupont jean
jdupont@babacool.fr

Je suppose que c'est .find qui doit faire l'affaire mais je ne vois pas comment ????

Par avance Merci de ton aide conconbrr

[SfJ5Rpw8]

j'ai pas bien compris ton code , ote moi d'un doute elle ne fonctionne pas ta focntion InStr ...?

tu peu utiliser les fonctions de feuilles de calcul pour effectuer la recherche.. en suposant que ton "code chauffeur" soit dans la 1° colonne de ta feuille adresse ..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
dim iLg As integer  'Numéro de ligne conteant l'adresse
On Error Resume Next
iLg = 0
iLg= Application.WorksheetFunction.Match(stType,Sheets("Adresse").Columns(1), 0)
On Error goto 0

reste plus qu'as utiliser iLg (si > 0) ... pour retrouver les valeurs des différentes colonnes

[SFLEY]

Bonjour,

J'ai du mal a m'exprimer correctement alors je recommence:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Option Explicit
 
Sub Commentaires()
 
Dim Cell
 
 
For Each Cell In ActiveSheet.UsedRange
 
    If Cell.HasFormula Then
 
 
 
        With Cell
 
            On Error Resume Next
 
 
   .Comment.Delete
   .AddComment
   .Comment.Text Text:=Cell.Value 
   .Comment.Shape.TextFrame.AutoSize = True
 
 
     End With
    End If
 
 
 
 
Next
End Sub

Avec ce code je cré un commentaire sur chaque cellule ayant une formule et j'y affecte un commentaire avec la valeur de la cellule.

pour l'instant pas de problème ça roule !

Ensuite je voudrais pouvoir ajouter un autre champ à ce commentaire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Comment.Text Text:=Cell.Value & "VALEUR 2" 

Ce champ serait obtenu par :
1 la recherche de "Cell.value" dans une autre feuille.
2 puis on renverrai la valeur de la cellule d'à coté dans la variable VALEUR2.

Mais j'y arrive pas !

Je suppose qu'il y a du .find et du .offset ????


AU secours je coule ...........

[Invité]

Essaye ça, c'est l'une des possibilités :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub Commentaires()
Dim Cell
Dim ValSearch, Rep, ValFind
 
For Each Cell In ActiveSheet.UsedRange
  If Cell.HasFormula Then
    With Cell
      On Error Resume Next
      ' Valeur de la cellule à rechercher
      ValSearch = .Value
      ' Recherche le numéro de ligne contenant la valeur dans la colonne "A"
      Rep = Sheets("Feuil2").Range("A:A").Find(What:=ValSearch, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
      ' Récupère la valeur de la colonne "2"
      ValFind = Sheets("Feuil2").Cells(Rep, 2).Value
      ' Inscrit le commentaire
      .Comment.Delete
      .AddComment
      .Comment.Text Text:=.Value & " " & ValFind
      .Comment.Shape.TextFrame.AutoSize = True
    End With
  End If
Next
End Sub

Cordialement

[SFLEY]

je vais essayer ça !

merci

[SFLEY]

Bonjour,

Alors voila j'ai essayé la formule et ça marche pas ?

Donc j'ai ajouté un msgbox ici afin d'espionner la variable rep:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
' Récupère la valeur de la colonne "2"
        MsgBox Rep
      ValFind = Sheets("Feuil2").Cells(Rep, 2).Value
      ' Inscrit le commentaire

afin de voir le contenu de rep, et cette valeur est vide donc je essayé de la définir en integer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Dim Cell
Dim ValSearch, ValFind As String
Dim Rep As String

et là elle vaut toujours 0 ????????

merci de votre aide je sens qu'on est pas loin !

grand merci à Bruno qui à bien pigé ce que je voulais faire !

[Invité]

Plusieurs questions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
' Valeur de la cellule à rechercher
      ValSearch = .Value

1) Est-ce que ValSearch contient bien la valeur de recherche ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
' Recherche le numéro de ligne contenant la valeur dans la colonne "A"
      Rep = Sheets("Feuil2").Range("A:A").Find(What:=ValSearch, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row

2) Est-ce que la valeur à rechercher se trouve bien
a) dans une feuille nommée "Feuil2" ?
b) dans la colonne "A" ?

A+

[SFLEY]

salut,

Alors


1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 ' Valeur de la cellule à rechercher
      ValSearch = .Value
      MsgBox ValSearch

me permet de te confirmer que Valsearch contient bien la valeur de la cellule !

2) a) OUI, j'ai cré un classeur de test en laissant les attributs d'origine avec feuil1 et feuil2....

2) b) La valeur recherché se trouve bien dans la colonne A


Ah je viens de trouver ça marche si et seulement si il y a une formule dans la dans la colonne A de la feuille 1.

Pourquoi ?

Si tu as une idée ?

Merci c'est déja super je vais pouvoir l'utiliser !

[SFLEY]

Salut,

ce que je viens de dire précedement est faux ça fonctionne si le focus sur la feuill1 est placer en A.

est ce que ce ne serait pas le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For Each Cell In ActiveSheet.UsedRange

???????

Merci

[Invité]

Explication de la procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For Each Cell In ActiveSheet.UsedRange

Pour chaque cellule dans la feuille active, de la "portée" utilisée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If Cell.HasFormula Then

Si la cellule est une formule alors ....

Donc il faut effectivement :
1) Activer la feuille en question :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("NomFeuille").Activate

2) sélectionner la colonne souhaitée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveSheet.Range("A1").Select

3) Effectuer ensuite la procédure

Voilà

[SFLEY]

SALUT,

un grand merci à tous ça marche nickel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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