Transformé une formule Excel en script VBA ?

Version imprimable

29/11/2006, 20h39
SFLEY

Transformé une formule Excel en script VBA ?

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

:cry:
29/11/2006, 21h35
conconbrr

Salut,

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

Code:

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

devrait suffire.

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

Code:

.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 ;)
29/11/2006, 23h07
SFLEY

merci

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

Code:

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
-------
29/11/2006, 23h10
conconbrr

Utilises la fonction suivante :

Code:

.offset

Par exemple :

Code:

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

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

je progresse doucement......

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

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

:mrgreen:

03/12/2006, 16h26
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:

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

Je suis pas très clair !

Bonjour,

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

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:

.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 ...........:oops:

De retour ...

:salut:

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

Code:

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 ;)

05/01/2007, 17h23
SFLEY

merci

je vais essayer ça !

merci:D
05/01/2007, 17h53
SFLEY

c'est presque ça ?
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:

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:

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 !
;)

:salut:

Plusieurs questions :
Code:

1 2 ' Valeur de la cellule à rechercher ValSearch = .Value
1) Est-ce que ValSearch contient bien la valeur de recherche ?
Code:

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+

05/01/2007, 20h36
SFLEY

affirmatif !
salut,

Alors

1)
Code:

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 !
05/01/2007, 21h07
SFLEY

Idée ?

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:

For Each Cell In ActiveSheet.UsedRange

???????

Merci
05/01/2007, 21h52
Invité

:salut:

Explication de la procédure :

Code:

For Each Cell In ActiveSheet.UsedRange

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

Code:

If Cell.HasFormula Then

Si la cellule est une formule alors ....

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

Code:

Sheets("NomFeuille").Activate

2) sélectionner la colonne souhaitée :

Code:

ActiveSheet.Range("A1").Select

3) Effectuer ensuite la procédure

Voilà ;)

Merci

SALUT,

un grand merci à tous ça marche nickel :

Code:

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

;)