Discussion :

[Arone77]

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 :

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

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 :

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

[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.

[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 :

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

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

[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 :

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

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

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

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

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

Quel est la meilleure solution pensez vous ?

Merci

[joe.levrai]

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 :

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

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 :

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

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

[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 :

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

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 :

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

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

Merci encore... !!!

[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 :

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

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

la tienne :

Code :

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

"=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 :

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

"=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