Discussion :

[Philippe_shelby]

Bonjour,

Etant novice en VBA, cela fait un moment que je cherche une solution pour écrire un code de remplissage de cellule qui ne se base pas les colonnes mais sur des noms données aux colonnes.

Je m'explique : Je voudrais remplir différentes cellules d'un tableau à la dernière ligne utilisée a l'aide d'un UserForm, par contre, lorsque j'insère une colonne manuellement dans le fichier. toutes les données importées à l'arrière sont décalées d'une cellule.

J'ai nommé mes différents colonnes dans le tableur (ex : Nom colonne D = Emetteur et nom colonne O = Date_E))



Voici un morceau de mon code actuel. (désolé, je ne sais pas comment insérer un morceau de code dans une liste numérotée)

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
'Correspond au programme du bouton VALIDER
Private Sub CommandButton1_Click()
Dim L As Integer
Dim Commentaire As String
Commentaire = TextBox4.Text
Dim estouvert As Integer
Dim fich As Workbook
 
 
If MsgBox("Etes-vous certain de vouloir INSERER cette Non-Conformité ?", vbYesNo, "Demande de confirmation") = vbYes Then
 
'Verification si "Tbx source" est ouvert sinon l'ouvrir
estouvert = False
For Each fich In Workbooks
If fich.Name = "Tbx source.xlsm" Then estouvert = True
Next
If estouvert = False Then Workbooks.Open ("c:\CHEMIN\Tbx source.xlsm")
 
 
'Insertion des données dans "Tbx source"
L = Workbooks("Tbx source.xlsm").Sheets("tbx source").Range("D65536").End(xlUp).Row + 1 'Permet de se positionner sur la dernière ligne de tableau NON VIDE
Workbooks("Tbx source.xlsm").Sheets("tbx source").Range("D" & L).Value = Label1
Workbooks("Tbx source.xlsm").Sheets("tbx source").Range("O" & L).Value = Label4
 
 
End If
End Sub

Merci pour votre aide.
Philippe

[Philippe Tulliez]

Bonjour,

Je m'explique : Je voudrais remplir différentes cellules d'un tableau à la dernière ligne utilisée a l'aide d'un UserForm, par contre, lorsque j'insère une colonne manuellement dans le fichier. toutes les données importées à l'arrière sont décalées d'une cellule.

Pour rendre dynamique ce type d'action, je préconise l'utilisation de la fonction EQUIV (MATCH en version anglaise et VBA) ou la méthode Find de l'objet Range. Dans ces deux cas, en faisant une recherche sur l'étiquette de colonne nous pourrons connaître le n° de la colonne désirée.
Exemple avec Match

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub test()
 Dim rng As Range, rng_Column As Range, ColumnPosition As Long, c As Range
 Const SearchValue As String = "Adresse"
 Set rng = ThisWorkbook.Worksheets("Membre").Range("A1").CurrentRegion ' Equivalent à Ctrl + A ou Ctrl + *
 ColumnPosition = Application.WorksheetFunction.Match(SearchValue, rng.Resize(1), 0) ' Position de l'étiquette en ligne 1 
 Set rng_Column = rng.Columns(ColumnPosition).Cells
 For Each c In rng_Column ' parcoure l'ensemble des cellules de la colonne
  Debug.Print c.Value
 Next
End Sub

La procédure ci-dessus sélectionne (variable Objet nommée rng_Column) la colonne dont l'étiquette de colonne est Adresse.
Ainsi lors de l'insertion d'une voire plusieurs colonnes, il n'y aura aucun problème pour la sélectionner


On n'est bien entendu pas obligé de passer par une variable objet intermédiaire
Voici un autre exemple

Code :

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

1
2
3
4
5
6
7
8
9
Sub test()
 Dim rng As Range, ColumnPosition As Long, c As Range
 Const SearchValue As String = "Adresse"
 Set rng = ThisWorkbook.Worksheets("Membre").Range("A1").CurrentRegion
 ColumnPosition = Application.WorksheetFunction.Match(SearchValue, rng.Resize(1), 0)
 For Each c In rng.Columns(ColumnPosition).Cells
  Debug.Print c.Value
 Next
End Sub

Et j'ajouterais qu'idéalement on devrait faire en sorte de n'utiliser que la partie Data, c'est à dire à partir de la ligne 2. Il faut alors utiliser les propriétés Offset et Resize combinées.
Exemple

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub test()
 Dim rng As Range, ColumnPosition As Long, c As Range
 Const SearchValue As String = "Adresse"
 Set rng = ThisWorkbook.Worksheets("Membre").Range("A1").CurrentRegion
 ColumnPosition = Application.WorksheetFunction.Match(SearchValue, rng.Resize(1), 0)
 With rng ' Utilise uniquement la partie données
  Set rng = .Offset(1).Resize(.Rows.Count - 1)
 End With
 For Each c In rng.Columns(ColumnPosition).Cells
  Debug.Print c.Value
 Next
End Sub

[Qwazerty]

Salut

Pour ma part je conseille l'utilisation des tableau (Onglet Insertion->Tableau)
Tu peux ensuite le renommer en cliquant n'importe où dans le tableau, un onglet spécifique va apparaitre dans le bandeau excel avec des options.

Pour ce qui est de l'utilisation dans le code ça ressemble à ça

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
    'On pointe sur le tableau pour simplifier les écritures
    With Feuil1.ListObjects("Tab_Traitement")
        'On boucle sur les lignes du tableau Tab_Traitement
        For Each TheRow In .ListRows
            'On regarde si la colonne création contien quelques choses
            NonCreer = .ListColumns("Création").DataBodyRange.Cells(TheRow.Index, 1) = ""
 
            'On mémorise le pilote et on regarde si affecté
            Pilote = .ListColumns("Pilote").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 20).Value
            PiloteChoisi = Pilote <> ""
 
            'On teste si la ligne doit être traité
            If NonCreer And PiloteChoisi Then
                ' Entre les données de la NC dans les variables
                DateCode = .ListColumns("N° NC").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 2).Value
                DateNC = .ListColumns("DATE").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 5).Value
                Reference = .ListColumns("PRODUIT").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 6).Value
                Designation = .ListColumns("DESIGNATION").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 7).Value
                Fournisseur = .ListColumns("CLIENT").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 8).Value
                Effet = .ListColumns("NC détectée").DataBodyRange.Cells(TheRow.Index, 1) 'Cells(i, 9).Value

Si tu veux un exemple complet voila la discussion d'où est issu le morceau de code.

++
Qwaz

[Philippe Tulliez]

Bonjour Stéphane,
C'est effectivement une autre solution tout aussi intéressante.

[Philippe_shelby]

Merci à vous pour vos réponses...

@Qwazerty : Pour un novice, cette solution parait plus simple à mettre en œuvre. Par contre, pourriez-vous juste me donner la syntaxe pour une entrée de donné dans ce type de tableau du style :

Code :

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

 Sheets("Feuil1").Range("D" & L).Value = ComboBox3.Value.

Merci.

[Qwazerty]

Salut

Tout simplement en inversant les termes de l'exemple donné au dessus

Code :

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

1
2
DateCode = .ListColumns("N° NC").DataBodyRange.Cells(TheRow.Index, 1)

Devient

Code :

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

1
2
.ListColumns("N° NC").DataBodyRange.Cells(TheRow.Index, 1) = DateCode

Voila un exemple concrêt

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
Sub teste()
'Déclaration d'un objet Row (qui représente une ligne du tableau
Dim NewRow As ListRow
 
    'On simplifie l'écriture (attention Feuil3 est le codeName de la feuille pas le nom contenu dans l'onglet sous Excel (voir image liée au poste
    With Feuil3.ListObjects("Tab_Taux")
        'On créee une nouvelle ligne et on la pointe avec NewRow
        Set NewRow = .ListRows.Add
 
        'On renseigne les données
        'dans la 1ère cellule de la nouvelle ligne
        .ListColumns("NomDeLaColonne").DataBodyRange.Cells(NewRow.Index, 1) = label1.Text
        'Puis dans la cellule suivante
        .ListColumns("NomDeLaColonne").DataBodyRange.Cells(NewRow.Index, 2) = Label3.Text
 
        '[...]
 
        'Si tu veux supprimer une ligne
        .ListRows.Item(2).Delete
 
    End With
End Sub

N'hésite pas à utiliser l'aide VBE pour savoir à quoi correspondent les différents membre (touche F1).
DataBodyRange par exemple ne tient pas compte de l'entête du tableau, uniquement des données.

Pour le codeName c'est ici que ça se passe


++
Qwaz

@Philippe Tulliez : Oui j'aime bien utiliser les tableaux, ils offrent de bonnes possibilités et le code s'en trouve simplifié. Je trouve que ça a été développé intelligemment.
J'ai pas testé l'écriture sur le blog, je ferais peut-être un petit billet s'il n'y a pas déjà des choses

[Philippe_shelby]

Merci, je crois que j'ai compris. Demain matin j'essaye d'adapter mon code et je vous tiendrais au courant.

[Philippe_shelby]

Bonjour,

Help.....Je ne m'en sors pas.....

J'ai essayé la solution de Qwazerty et je n'arrive toujours pas à remplir les cellules de mon tableau proprement. sans utiliser la méthode " cells(1,1)"

J'ai créé deux petits fichiers tout basiques.

Est-ce que quelqu'un pourrais juste, SVP, me coder ces deux affectations de cellule pour que j'ai un exemple de syntaxe.
Est-ce qu'il est possible d’insérer une ligne sous la dernière ligne remplie du tableau destination avant l'écriture dans celle-ci ?

Pour rappel, je voudrais pouvoir par la suite insérer de nouvelles colonne dans mon tableau sans être obligé de réaffecter tout le code d'insertion des valeurs dans mon fichier destination.

Merci d'avance.

Philippe.