Inputer une formule dans la colonne d'un tableau importé via une macro

Version imprimable

Bonjour !

Je demande votre aide puisque je n'arrive pas à résoudre mon problème ! En effet, j'ai réussi à coder pour pouvoir importer un fichier excel et le mettre dans un tableau de mon outil VBA.
Le hic, c'est que quand j'ajoute un tableau excel dans mon document excel, les lignes qui s'ajoutent ne prennent pas en compte les formules présentes dans mon tableau.

Du coup, j'ai essayé d’insérer mes formules RECHERCHEV dans mon code pour qu'au moment où je copie mon tableau soit copié également ma formule suivante dans la colonne 11 :

Code:

ActiveCell.Offset(0, 11).FormulaLocal = "= SIERREUR((RECHERCHEV([Voiture];TABLEAUVENTE;2;FAUX);Information manquante))"

Le code pour importer un tableau dans mon document est le 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 Sub importerDATA() Dim nomFichier As String Dim wkbSrc As Workbook ' classeur à importer Dim wsSrc As Worksheet, wsCible As Worksheet Dim cellCible As Range Dim zoneImporter As Range Dim dernierNum As Integer, i As Integer Dim Lr As ListRow Feuil1.Unprotect Feuil2.Unprotect Set wsCible = ThisWorkbook.Worksheets("Base de données") nomFichier = Application.GetOpenFilename("Fichiers Excel (*.xls*), *.xls*") If nomFichier <> "Faux" Then Set wkbSrc = Workbooks.Open(nomFichier) Set wsSrc = wkbSrc.Worksheets(1) Set zoneImporter = Range(wsSrc.Range("A2"), wsSrc.Cells(derniereLigne(wsSrc), derniereColonne(wsSrc))) Set cellCible = wsCible.Cells(derniereLigne(wsCible, 18) + 1, 1) zoneImporter.Copy cellCible.PasteSpecial xlPasteValues cellCible.CurrentRegion.EntireRow.AutoFit Application.DisplayAlerts = False wkbSrc.Close False Application.DisplayAlerts = True End If Feuil1.Protect Feuil2.Protect End Sub
Merci de votre aide !!!

09/09/2016, 16h47
jerome.vaussenat

Salut,

Si tu as au moins Excel 2007 et plus, tu n'as pas besoins de macro et encore moins de VBa pour recopier des formules quand tu ajoutes des données à ton tableau.

Pour ton bonheur et le notre, Microsoft à créé une fonctionnalité : Tableau (et oui, ils sont forts chez Microsoft !

Si tu active cette fonction Excel fera automatiquement :
Activation des filtres.
Conservation de la ligne de titre à l'écran en permanence (sans avoir à figer les volets).
Recopie AUTOMATIQUE des formules à la mise en place des formules ou à l'insertion des données (par insertion de lignes ou copier coller) : on parle de colonnes calculées.
Application d'une mise en forme très visuelle avec alternance de lignes claires et foncées (alternance conservée à l'insertion de linge, bien sûr)
Prise en compte des données ajoutées à la fin du tableau dans un TCD (après actualisation.

Et je crois que c'est tout mais c'est déjà pas mal !

Bon comment on fait çà ?
Place toi dans ton tableau
Dans l'onglet Accueil
Clique sur Mettre sous forme de tableau (et en plus tu peux choisir la mise en forme qui s'applique !)
Tu valides la boîte de dialogue et ...
C'est tout.

Pour info, toutes les cellules fusionnées de ton tableau seront dé fusionnées (d'abord, dans Excel, on ne fusionne pas. et c'est tout)

En espérant que cela te facilitera la vie.
09/09/2016, 16h53
joe.levrai

Bonjour,

outre la solution du tableau structuré présenté par jérome, et qui est la solution la plus efficace selon moi ...

deux solutions moins jolies :

1) écriture de la formule sur l'ensemble de la colonne 11 de la plage utilisées (sauf la ligne 1 qui contient les titres)
2) effectuer une recopie de la formule (AutoFill pour étirer la formule)

pour la solution 1, ligne de code à placer entre la ligne 22 et 23 de ta procédure présentée

Code:

wsCible.Cells(2, 11).Resize(.UsedRange.Rows.Count - 1, 1).FormulaLocal = "=SIERREUR(RECHERCHEV(Voiture;TABLEAUVENTE;2;FAUX);""Information manquante"")"

pour la solution 2 : essaye de le faire manuellement avec l'enregistreur de macro, tu auras la méthode :zoubi:
09/09/2016, 17h25
Arone77

Bonjour,

Merci pour vos réponses !!!
je suis bien d'accord avec vous que lorsqu'on crée un tableau, une formule s'applique à une colonne entière. Mais dans mon cas, je ne sais pas pourquoi mais la formule ne s'ajoute pas aux cellules qui se sont ajoutés suite à l'importation de mon tableau .xls ! Et comme à terme, la feuille sera protégée, l'utilisateur ne pourra pas faire un glissé pour développer la formule jusqu'à la dernière ligne.

Du coup, je suis intéressé par votre première formule mais malheureusement, la formule bloque sur le code

Code:

.usedrange

où le message d'erreur suivant apparaît: "référence incorrect ou non qualifié". :/

Du coup j'ai essayé l'autre solution mais le fait d’insérer le code

Code:

Selection.AutoFill Destination:=Range("BASEDEDONNEES[VOITURE]")

mais le message "la méthode range a echoué apparait... :(

Quel est la meilleure solution pensez vous ?

Merci :)

Désolé, quand j'ai testé, j'avais découpé dans des blocs With/End With

j'ai oublié de remettre au propre

devant .UsedRange tu ajoutes wsCible

Code:

wsCible.Cells(2, 11).Resize(wsCible.UsedRange.Rows.Count - 1, 1).FormulaLocal = "=SIERREUR(RECHERCHEV(Voiture;TABLEAUVENTE;2;FAUX);""Information manquante"")"

pour l'erreur sur la méthode autofill, c'est normal car tu fais références à une colonne de ton tableau structurée d'une mauvaise façon

ceci devrait être mieux, j'en profite pour te présenter l'objet "ListObject" qui est l'objet "tableau structuré" côté VBA
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub toto() ' Si tu veux utiliser une variable pour ton tableau 'Dim MonTableau As ListObject 'Set MonTableau = ThisWorkbook.Worksheets("Base de données").ListObjects("BASEDEDONNEES") ' Listcolumns représente la colonne ' DataBodyrange représente la zone des données du tableau With ThisWorkbook.Worksheets("Base de données").ListObjects("BASEDEDONNEES").ListColumns("VOITURE") .DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange End With End Sub

09/09/2016, 18h19
Arone77

Je suis désolé, j'ai vraiment l'impression d'être pas bon malgré votre aide.

Votre premier code bug encore en me disant "erreur définie par l'application ou par l'objet"
Pourtant, j'ai écrit :

Code:

wsCible.Cells(19, 12).Resize(wsCible.UsedRange.Rows.Count - 1, 1).FormulaLocal ="=SIERREUR((RECHERCHEV(Voiture;TABLEAUVENTE;2;FAUX);Information manquante))"

Votre deuxième code me dit "erreur d'écécution '1004':
erreur définie par l'application ou par l'objet".

C'est sur cette ligne que ca bug...

Code:

.DataBodyRange.Cells(19, 12).AutoFill .DataBodyRange

Merci encore... !!! :)
09/09/2016, 19h00
joe.levrai

Je suis parti du principe :

- que nous sommes en présence d'un tableau structuré (ListObject) appelé BASEDEDONNEES, et contenant une colonne dont l'en-tête est VOITURE
- que les données commencent en ligne 2 (avec donc la ligne de titres en 1)

Est-ce bien le cas ?

Si oui, alors les codes que je t'ai proposé n'ont pas a être modifiés, tu as changé le positionnement du Cells(x,y) je vois
Si non, tu peux indiquer où se trouve la ligne de titre ?

1) Pour le problème avec l'écriture de la formule :

Je note que tu as aussi modifié la formule, en remettant une parenthèse ouvrante et fermante et en oubliait de mettre des doubles parenthèses avant et après ton "Information manquante"

ma proposition :

Code:

"=SIERREUR(RECHERCHEV(Voiture;TABLEAUVENTE;2;FAUX);""Information manquante"")"

la tienne :

Code:

"=SIERREUR((RECHERCHEV(Voiture;TABLEAUVENTE;2;FAUX);Information manquante))"

Ce à quoi je remarque une éventuelle coquille dans ma proposition initiale car je pensais que voiture était une plage nommée !
Comme je ne suis pas sûr de ce que dois faire ta formule, voici une proposition

Code:

"=SIERREUR(RECHERCHEV([@Voiture];BASEDEDONNEES[#Tout];2;FAUX);""Information manquante"")"

2) Pour l'erreur avec Autofill

Ma proposition partait de la première cellule de la colonne "VOITURE" pour étirer la formule sur toute la colonne.

De ton côté, non seulement tu pars de la 19è cellule ... mais sur 12ème colonne après la colonne "VOITURE" !

En effet, le bloc "With" de ma proposition se place sur la colonne "VOITURE"
Donc le .Cells(1,1) équivaut à la première "VOITURE", .Cells(2,1) à la seconde VOITURE ..... et si tu changes le deuxième chiffres tu vas changer de colonne en te décalant.

Ca génère deux problèmes :

- qui ne fera pas planter le code si tu as suffisament de colonnes : tu va recopier les formules de la mauvaise colonne avec le "12" de ton .Cells(19,12)

- qui fait planter ton code : on ne peux faire une copie de formule que dans un sens à la fois (haut / bas / gauche / droite). Or, étant sur la ligne 19, tu demandes à VBA de faire une recopie sur toute la colonne. C'est à dire que tu lui demandes de remplir les 18 cellules au dessus, et celles en dessous. D'où l'erreur
12/09/2016, 13h44
Arone77

2 pièce(s) jointe(s)

Bonjour,

Malgré toutes vos indications, je ne parviens pas à corriger le message d'erreur. De ce fait, je dépose un fichier excel contenant dans le module 1 le code d'importation d'un tableau excel où le message d'erreur apparaît à la ligne permettant de mettre en place la formule rechercheV.
Je glisse également un tableau type qui sera importé dans le fichier pour agrémenter la base de données.

Je souligne que dans un soucis de clareté, j'avais échangé dans mes explications les termes "N° de siret/siren" par "voiture" et "MOAPUBLICEP" par "BASEDEDONNEES". C'est à partir de la ligne 19 que le tableau doit pouvoir être inséré. La formule commence donc ligne 19, colonne 12.

Je vous remercie par avance de votre expertise, merci 1000 fois,

Cordialement
https://we.tl/JrOg8Sck7P
Pièce jointe 220120

Bonjour,

difficile de tester, mais selon moi, ma proposition manipulant le listobjects devrait parfaitement fonctionner :

Code:

1
2
3
With wsCible.ListObjects("BASEDEDONNEES").ListColumns("VOITURE")
            .DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange
        End With

Il suffit de remplacer VOITURE par le nom réel de la colonne du tableau (et si besoin changer le nom du tableau BASEDEDONNEES)

ce qui donnerait probablement :

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
Sub importerDATA()
Dim nomFichier As String
Dim wkbSrc As Workbook ' classeur à importer
Dim wsSrc As Worksheet, wsCible As Worksheet
Dim cellCible As Range
Dim zoneImporter As Range
 
Feuil1.Unprotect
 
Set wsCible = ThisWorkbook.Worksheets("Base de données")
nomFichier = Application.GetOpenFilename("Fichiers Excel (*.xls*), *.xls*")
 
If nomFichier <> "Faux" Then
 
    Set wkbSrc = Workbooks.Open(nomFichier)
    Set wsSrc = wkbSrc.Worksheets(1)
    Set zoneImporter = wsSrc.Range(wsSrc.Range("A2"), wsSrc.Cells(derniereLigne(wsSrc), derniereColonne(wsSrc)))
    Set cellCible = wsCible.Cells(derniereLigne(wsCible, 18) + 1, 1)
 
        zoneImporter.Copy
        cellCible.PasteSpecial xlPasteValues
 
    With wsCible.ListObjects("BASEDEDONNEES").ListColumns("VOITURE")
        .DataBodyRange.Cells(1, 1).AutoFill .DataBodyRange
    End With
 
    Application.DisplayAlerts = False
        wkbSrc.Close False
    Application.DisplayAlerts = True
End If
 
Feuil1.Protect
 
End Sub

Joe.levrai,

Merci de votre aide !!! Infiniment ! je suis d'accord avec vous que la solution "autofill" marche ! Mais j'ai insisté sur l'autre formule puisque pour la suite de mon outils, celle ci est je pense indispensable.

En effet, une fois que ma base de données est actualisée avec l'import de mon tableau excel, j'ai crée un userform qui effectue une recherche et sélectionnent les lignes qui correspondent aux trois critères suivant:
- intervalle de date
- département
- MOA

Avec la méthode autofill, lorsque j'effectue ma recherche et que mes lignes, sélectionnées, se collent dans mon tableau, la colonne "MOA" apparait avec le code suivant (ce qui fait bugger la formule puisque nous ne sommes plus dans le tableau BASEDEDONNEES mais dans RESULTATD'ETUDE):

Code:

=SIERREUR(RECHERCHEV(BASEDEDONNEES[N° de SIRET/SIREN];MOAPUBLICEP;2;FAUX);"Information manquante")

Si j'arrive à coder dès le début avec la ligne de code, je n'aurai plus de problème puisque dans chaque tableau, je forcerai la formule pour ma colonne MOA:

Code:

wsCible.Cells(2, 11).Resize(wsCible.UsedRange.Rows.Count, 1).FormulaLocal = "=SIERREUR(RECHERCHEV([N° de SIRET/SIREN];MOAPUBLICEP;2;[FAUX]);""Information manquante"")"

,

je pense pouvoir réutiliser ce code dans celui me permettant de sélectionner mes lignes de ma base de données:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sub rechercherDB(dateDebut As Date, _ datefin As Date, _ MOA As String) Dim ligne As Long, Lr As ListRow Feuil1.Unprotect Feuil2.Unprotect 'Permet de supprimer les lignes initialement présentes en faisant une nouvelle recherche ligne = 1 If Feuil2.ListObjects(1).ListRows.Count > 0 Then Feuil2.ListObjects(1).DataBodyRange.Rows.Delete With Feuil1.ListObjects(1).DataBodyRange 'permet de sélectionner en fonction des dates et MOA choisie For ligne = 1 To .Rows.Count If .Cells(ligne, 1) >= dateDebut And .Cells(ligne, 1) <= datefin And .Cells(ligne, 12).Value = MOA Then Set Lr = Feuil2.ListObjects(1).ListRows.Add .Rows(ligne).Copy Lr.Range(1, 1) End If Next End With Feuil1.Protect Feuil2.Protect End Sub
En forçant la formule dans la colonne, je n'aurai plus de problème pour passer d'une étape à l'autre...

Ai je été clair :p ?

Amicalement !

13/09/2016, 17h57
joe.levrai

Si j'ai bien compris, dans ta colonne où il y a les formules, suivant les lignes, la source n'est pas le même tableau, et il faut donc pouvoir faire coexister les deux formules différentes ?
14/09/2016, 08h43
Arone77

Non, ce n'est pas çà. Mon organisation est la suivante:

J'ai deux tableau et un userform:
- un tableau "BASEDEDONNEES" (c'est là que j'insère le fichier .xls importer)
- un userform qui me permet de faire une recherche sur ma base de données et sélectionné les lignes correspondant à celle ci (C'est le code que j'ai glissé à ma dernière publication)
- un tableau "RESULTATDEL'ETUDE" (Les lignes qui ont été sélectionné par ma recherche sont collés dans ce tableau. Ensuite, j'ai un graphique croisé dynamique qui me sort mes analyses de marché). Les deux tableaux sont identiques dans leur structure.

En faisant la méthode autofill, la formule est bien présente dans ma colonne "MOA" de mon tableau "BASEDEDONNEES".
Mais lorsque mes lignes sont collés dans mon deuxième tableau, la formule de ma colonne "MOA" est également collée.
Le problème, c'est qu'elle est collé en fonction du tableau "BASEDEDONNEES". Du coup, la fonction recherchev n'identifie plus le numéro de siren puisqu'il recherche

Code:

=SIERREUR(RECHERCHEV(BASEDEDONNEES[N° de SIRET/SIREN];MOAPUBLICEP;2;FAUX);"Information manquante")

Je suis obligé de supprimer manuellement "BASEDEDONNEES" pour que "[N° de SIRET/SIREN]" soit assimilé à mon nouveau tableau. Pour l'instant, j'ai donc que des informations manquantes qui apparaissent dans mon tableau !

De plus, la méthode autofill ne marche pas si à la première cellule, la formule n'est pas présente. Mon tableau pourra potentiellement être vider avant d'être rempli de nouveau.

C'est pour ces raisons que je préfère votre méthode numéro 1, qui permet de coller la formule dans la colonne [MOA]. En effet, même dans mon code de sélection, je pense que je pourrai l'insérer et ne plus me retrouver dans le problème actuelle !

Bonjour,

ceci part de la ligne 19 et colonne 11, et remplis, la formule que tu souhaites (je pense).
le remplissage s'arrête à la première ligne vide du tableau

il existe au minimun une ligne vide au dessus et en dessous du tableau
Code:

1 2 3 4 5 6 7 8 Feuil1.Unprotect Set wsCible = ThisWorkbook.Worksheets("Base de données") With wsCible.Cells(19, 11) .Resize(.CurrentRegion.Rows.Count - 1, 1).FormulaLocal = "=SIERREUR(RECHERCHEV([N° de SIRET/SIREN];MOAPUBLICEP;2;FAUX);""Information manquante"")" End With Feuil1.Protect