Discussion :

[mel--]

Bonjour,

Je suis vraiment vraiment désolée d'être encore ici, mais cela fais depuis ce matin que je cherche une solution et... malheureusement... je ne trouve pas.

J'effectue, sur un fichier, des boucles pour appliquer des formules excel dans un tableau de données.

J'arrive à appliquer les formules ainsi qu'a changer le format de mes cellules (de texte à standard) via vba. Malgré tout, ma formule reste écrite telle quelle dans la cellule, sans se calculer. Je précise que le calcul automatique d'excel est bien activé (dans option -> formule -> mode de calcul -> automatique est coché). Il faut également dire que lorsque je double click dans la cellule, le calcul s'effectue (comme si la cellule avait juste besoin d'une forme d"actualisation").

Voici mes codes :

Code :

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

1
2
3
4
5
For I = 2 To derligne
Val_recherchee = Cells(I, J - 3)
    J = 7
        Cells(I, J).Formula = "=RECHERCHEV(" & CStr(Val_recherchee) & ";Gestionnaires!$A$2:$M$2337;9;FAUX)"
Next

Voici une autre formule qui ne se calcuel pas (aucune formules ne se calculent, je mets juste deux exemples)

Code :

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

1
2
3
4
5
For I = 2 To derligne
    J = 7
    Val_recherchee = CStr(Cells(I, J).Value)
    Cells(I, J + 1).Formula = "=CONCATENER(""VIL"";DROITE(" & Val_recherchee & ";7))"
    Next

Et voici le code pour le format

Code :

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

1
2
3
4
5
For I = 2 To derligne
J = 7
Set cell = Cells(I, J)
cell.NumberFormat = "General"
Next

Par avance je vous remercie énoooooormément

[Pierre Fauconnier]

Salut.

Si tu travailles dans une table de données (tableau structuré), tu n'es normalement pas obligé de boucler. Excel "le fait" pour toi. Il te suffit donc de placer la formule dans une cellule (la première par exemple) de la colonne de la table. Cela étant, tableau structuré ou pas, tu peux définir la plage avant et y appliquer la propriété Formula en un bloc sans boucler. Le but de la formule n'est pas d'être différente à chaque ligne, ce que réalise ton code, mais bien d'être identique de ligne en ligne. Tu as donc intérêt à l'écrire une fois en Excel en utilisant la référence à la cellule contenant la valeur cherchée, puis à l'utiliser en VBA.

De plus, si tu utilises Formula (ce que je conseille fortement), tu dois écrire la formule en anglais.




Pour les tableaux illustrés ci-dessus, le code suivant donne donc la solution en une ligne de code (pour un tableau structuré): Range("t_Personnel[Taux]").Formula = "=VLOOKUP([@Service],t_Services,2,0)". Pour une plage de données classique, il faudrait définir le Range avant d'y appliquer la formule, mais le principe reste identique.

[mel--]

Je précise que j'ai déjà utilisé l'enregistreur de macro et que le code rendu ne fonctionne pas.

[mel--]

Oui et bien cela est fort étrange car lorsque je mets ma formule en anglais, cela ne fonctionne plus.

Même si je met formulaR1C1 cela fonctionnais mieux en français qu'en anglais (dans le sens ou j'ai moins d'erreur en français car j'ai du réécrire ma formule au moins 50 fois avant que je n'arrive à la faire fonctionner - quel mystère).

très bien ça marche!

Je vais essayer de définir une plage pour y inscrire ma formule.

[mel--]

"Le but de la formule n'est pas d'être différente à chaque ligne, ce que réalise ton code, mais bien d'être identique de ligne en ligne."

Je n'ai pas du tout compris cette phrase...

Je sais qu'il est beaucoup plus simple d'écrire les formules en excel directement, mais la consigne est de tout rendre automatique, y compris les formules excel.

[Pierre Fauconnier]

Pour retrouver la formule en anglais, utilise la technique que j'ai illustrée. Ecris la formule dans une cellule, puis dans la fenêtre d'exécution du VBA, saisis la ligne de code comme dans mon illustration. Tu obtiendras alors la formule en anglais.

Pour borner ta plage, tu devrais utiliser des tables de données. Si tu ne "peux" pas, déclare un objet de type Range et borne-le.

[mel--]

Hummm.... J'ai encore beaucoup de choses à apprendre car je ne comprends pas tout.

Un très grand merci pour les cours! Je vais regarder attentivement car je ne cerne pas bien la notion de "tableaux structurés".

Oui j'utilise régulièrement la formule vlookup, je l'utilise en français ou en anglais en fonction de se qui marche en premier (je pense que ce n'est pas la meilleure chose a faire).
Pour l'instant je suis sur l'option de borner un objet type range (car je ne comprends pas les autres options).

Comme cela:

Code :

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

1
2
3
4
5
6
Dim ma_plage as range
dim derligne as integer
dim dercolonne as integer
 
derligne=Range("A" & Rows.Count).End(xlUp).Row
set ma_plage = range( 1 & derligne & ":" & ":G")

Un très grand merci pour votre temps et votre écoute

[Pierre Fauconnier]

Tu dois recomposer l'adresse de la plage comme dans Excel

Code :

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

set ma_plage = range("A1:G" & derligne)

[Pierre Fauconnier]

"Le but de la formule n'est pas d'être différente à chaque ligne, ce que réalise ton code, mais bien d'être identique de ligne en ligne."

Je n'ai pas du tout compris cette phrase...

Dans le code que tu as donné en entrée de discussion, tu composes à chaque boucle une formule qui reprend "en dur" la valeur à chercher. Ce n'est pas comme cela qu'on procède, car les boucles représentent un traitement assez lent sur une plage Excel. Sur quelques (centaines de) lignes, ça peut être acceptable, mais sur des milliers de lignes, ça vite devenir très lent. L'idée est donc de borner la plage puis de "balancer" la formule en VBA sur la plage, comme le montre mon exemple. Pour cela, on recompose la formule "telle qu'on l'utiliserait en Excel" et on l'utilise comme une chaine de texte en VBA, soit avec FormulaLocal si on veut écrire la formule en français dans le vba, soit avec Formula si on l'écrit en anglais. D'une façon générale, en l'écrit en anglais avec Formula pour passer au travers des problèmes de version linguistique d'Excel, car comme son nom l'indique, FormulaLocal utilise la langue d'Excel, de sorte que ton code ne pourra être utilisé que sur des Excel qui fonctionnent dans cette langue, alors que Formula permettra que ton code fonctionne sur n'importe quelle version linguistique de l'outil.

Je sais qu'il est beaucoup plus simple d'écrire les formules en excel directement, mais la consigne est de tout rendre automatique, y compris les formules excel.

La solution que j'apporte permet le "tout automatique". Elle accélère simplement le traitement en se passant de la boucle. Si, par la suite, on veut n'avoir que les valeurs et non les formules, on utilise la ligne de code suivante pour "copier/coller en spécial valeurs": MaPlage.Value = MaPlage.Value

Code :

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

1
2
3
4
Sub TestVlookup()
  Range("t_Prénoms[Taux]").Formula = "=VLOOKUP([@Service],t_Services,2,0)"
  Range("t_Prénoms[Taux]").Value = Range("t_Prénoms[Taux]").Value
End Sub

[...]
Un très grand merci pour les cours! Je vais regarder attentivement car je ne cerne pas bien la notion de "tableaux structurés".[...]

Les tableaux structurés apportent une grande souplesse à ton code car tu n'as justement rien à borner. Tu les utilises avec leur nom (de tableau ou de colonne), ils s'adaptent à l'ajout ou la suppression de données, au déplacement à l'intérieur d'une feuille ou sur une autre feuille sans devoir modifier une ligne de code. Sur base d'une table nommée Tableau1, tu peux utiliser Range("Tableau1") partout dans un code bien architecturé sans devoir te préoccuper ni de l'emplacement ni de la taille de ta table. En 2019, pratiquer rechercheV sur des plages "classiques" est pour moi un non-sens, et je ne peux que recommander d'utiliser systématiquement les tables de données ou tableaux structurés pour stocker sur des feuilles Excel des données qui se conçoivent telles des enregistrements (ou des fiches, si tu préfères), tel que je l'explique dans le tuto cité précédemment.

[mel--]

Bonjour Pierre,

Excusez moi de ne pas vous avoir répondu plus tot.

Merci pour toutes ces précisions. Je vais donc procéder comme vous le dites.

Je posterais mon code lorsqu'il marchera (cela risque donc de prendre du temps xD ).

Encore un fois merci pour ce cours, c'est grâce à des personnes comme vous que je peux progresser.

Je vous souhaite une bonne journée

[mel--]

Re-bonjour,

Alors, je comprends maintenant en quoi consiste un tableau structuré, qui est, comme vous le dites, un outils fort pratique (même si je n'ai pas lu la totalité du cours pour l'instant).

J'ai mis en place ma table de recherche (f_gestionnaire), la valeur recherchée (val_recherchee) ainsi que la plage ou je souhaitais que ma formule se calcule (c_matricule).

Tout fonctionne parfaitement et sans boucle. (Par contre, je n'arrive pas à faire fonctionner la formule en anglais.)
Voici donc mon code:

Code :

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

1
2
3
4
5
6
7
8
9
 
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$G$1:$G$206"), , xlYes).Name = "c_matricule"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$D$1:$D$206"), , xlYes).Name = "val_recherchee"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$M$8342"), , xlYes).Name = "f_gestionnaire" 
 
Range("c_matricule").Formula = "=RECHERCHEV(val_recherchee; f_gestionnaire; 9; 0)"
 
        Columns("G:G").Select
        Selection.NumberFormat = "General"

En revanche, j'ai toujours ce même soucis de formule qui ne se calcule pas. La formule se calcule si je fais la démarche suivante:
données ->convertir -> délimité-> aucun spérateur -> terminer.

Or je n'arrive pas à écrire cette manipulation en vba. Sauvegarder n'a pas d'effet. La format de ma cellule est déjà en standard.

Par avance merci

[mel--]

Re-bonjour,

autre information importante, ma ligne:

Code :

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

1
2
 
Range("c_matricule").value = Range("c_matricule").value

génère une erreur de méthode sur range (erreur 1004).

[Pierre Fauconnier]

Pour la formule en anglais, regarde le code que j'ai fourni. Avec Formula, la formule doit être rédigée en anglais selon la syntaxe anglaise. Eventuellement, change Formula ne FormulaLocal pour pouvoir la rédiger en français. Ton classeur ne sera utilisable qu'avec des versions françaises d'Excel mais je suppose que ce n'est pas bloquant.

Pour le problème d'erreur 1004, c'est probablement parce que tu n'as pas de plage (ou de tableau structuré) qui s'appelle c_Matricule.

A priori, tu n'as pas besoin de Select et certainement pas sur G:G.

[mel--]

Bonjour,

J'ai utilisé formulaLocal afin de pouvoir écrire la formule en français. Merci beaucoup

Voici le code final :

Code :

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

1
2
3
4
5
6
7
 
For I = 2 To derligne
J = 7
        Range("c_matricule").FormulaLocal = "=RECHERCHEV(val_recherchee; f_gestionnaire; 9; 0)"
        Columns("G:G").Select
        Selection.NumberFormat = "General"
Next

Je sais qu'une boucle est inutile et ttrèèèèès lourde, mais c'est le seul moyen que j'ai trouvé pour que la formule recherchev me retourne son résultat et non sa syntaxe.

Pierre, je vous remercie ENOOOOOOOOOOOOOOOOOORMEMENT!!!!!

Je n'y serai jamais arrivée sans vous

[mel--]

AH! dernière remarque,

En fait je crois (maintenant que cela fonctionne bien), que lorsque j'ai paramétré mes tableaux structurés, ils se sont agrandis (ma faute) de quelques lignes par rapport à mon tableau base.

Aussi, lorsque j'ai utilisé un range cela a généré une erreur.

[mel--]

Effectivement le select sur le g:g ne servait à rien