VBA créer une liste de cellules à parcourir

Le 12/06/2017 à 17:01

Bonjour à tous,

j'utilise un code VBA qui me permet de copier le contenu de certaines cellules de différents fichiers, pour à chaque fois les coller dans un fichier global. Encore une histoire de consolidation donc.

J'aimerais créer une liste de cellules à parcourir, en ce qui concerne les contenus à copier. Par exemple j'aimerais établir une liste contenant les cellules [E4, C9, H6, H7, H9, H10] et derrière pouvoir incrémenter une variable k qui pourrait parcourir cette liste de cellules.

Voici mon code avec la variable k que j'aimerais utiliser mais qui n'est donc pas définie:

   Dim i As Long

Sub Consolider_Simu()
    Dim S_Commande As Worksheet
    Dim Chemin As String
    Dim Extension As String

    Set S_Commande = ThisWorkbook.Sheets("Commande")
    Chemin = S_Commande.Cells(3, 2).Value
    Extension = S_Commande.Cells(4, 2).Value

    Nb = BoucleFichiers(Chemin, Extension)

End Sub

Function BoucleFichiers(Chemin As String, Extension As String) As Integer
    Dim Fichier As String

    BoucleFichiers = 0

    'Boucle sur tous les fichiers 'Extension' du répertoire 'Chemin'
    Fichier = Dir(Chemin & "*" & Extension)

    Do While Len(Fichier) > 0
        BoucleFichiers = BoucleFichiers + ChargerFichier(Chemin & Fichier)
        Fichier = Dir()
    Loop
End Function

Function ChargerFichier(NomFichier As String) As Integer
    Dim WB_TargetFichier As Workbook
    Dim TargetSheet As Worksheet
    Dim MainSheet As Worksheet
    Dim j As Long
    Dim k As Long

    Set WB_TargetFichier = Workbooks.Open(NomFichier)
    Set TargetSheet = WB_TargetFichier.Sheets("SIMULATION")
    Set MainSheet = ThisWorkbook.Sheets("Main")

    j = 1

    For i = 1 To 23

            TargetSheet.Range("E" & k).Select
            Selection.Copy
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteValues
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteFormat

            k = k + 1
            j = j + 1

    Next i

    ChargerFichier = ChargerFichier + 1
    Application.CutCopyMode = False
    WB_TargetFichier.Close savechanges:=False

End Function

Quelqu'un aurait-il une solution à mon problème ?

Je vous souhaite une bonne fin de journée !

SkillzZ

Le 12/06/2017 à 17:08

Bonjour SkillzZ, bonjour le forum,

Pas sûr d'avoir bien compris mais je ferais :

Dim PL As Range
Dim CEL As Range

Set PL = Application.Union(Range("E4"), Range("C9"), Range("H6:H7"), Range("H9:H10"))
For Each CEL in PL
'...
Next CEL
Le 12/06/2017 à 17:49

Bonjour ThauThème,

je pense que tu as bien compris c'est bien quelque chose de ce style que j'aimerais réaliser ! Je vais tester ça dès demain, je te remercie pour ton temps et ton attention ! Je te réponds demain que ça marche ou que ça ne marche pas !

Bonne soirée,

SkillzZ

Le 12/06/2017 à 18:14

Bonsoir,

Si je veux pouvoir parcourir des références de cellules, j'en fais un tableau :

Cel = Split("E4 C9 H6 H7 H9 H10")

Je peux alors parcourir ce tableau :

For i = 0 To UBound(Cel)

Cel(i) me renverra à chaque fois une référence de cellule et Range(Cel(i)) la cellule.

Cordialement.

Le 13/06/2017 à 09:26

Bonjour à tous !

Merci pour vos réponses. Pour le moment je n'ai testé que la solution de ThauThème mais je bloque car VBA m'indique une erreur sur la ligne suivante:

For Each CEL In PL

        For j = 2 To 23

          TargetSheet.Range("CEL").Select
            Selection.Copy
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteValues
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteFormat

        Next j

    i = i + 1
    Next CEL

Le message d'erreur est: la méthode Range a échoué. Je ne dois pas pouvoir utiliser Range car ça fait peut-être une redondance mais je ne sais pas comment faire autrement. Une idée ?

J'essaye ta solution en attendant MFerrand. Merci !

SkillzZ

Le 13/06/2017 à 09:44

Après avoir testé ta solution MFerrand je ne parviens pas à la faire fonctionner non plus .. J'ai une erreur du type "Objet requis" qui vient faire des siennes. À cet endroit là:

Sub Consolider_Repertoire_Loc2()
    Dim S_Commande As Worksheet
    Dim Chemin As String
    Dim Extension As String

    Set S_Commande = ThisWorkbook.Sheets("Commande")
    Chemin = S_Commande.Cells(3, 2).Value
    Extension = S_Commande.Cells(5, 2).Value

    Nb = BoucleFichiers(Chemin, Extension)

End Sub

Function BoucleFichiers(Chemin As String, Extension As String) As Integer
    Dim Fichier As String

    BoucleFichiers = 0

    'Boucle sur tous les fichiers 'Extension' du répertoire 'Chemin'
    Fichier = Dir(Chemin & "*" & Extension)

    Do While Len(Fichier) > 0
        BoucleFichiers = BoucleFichiers + ChargerFichier(Chemin & Fichier)
        Fichier = Dir()
    Loop
End Function

Function ChargerFichier(NomFichier As String) As Integer
    Dim WB_TargetFichier As Workbook
    Dim TargetSheet As Worksheet
    Dim MainSheet As Worksheet
    Dim j As Long
    Dim CEL As String
    Dim i As Long
    Dim k As Long

    Set WB_TargetFichier = Workbooks.Open(NomFichier)
    Set TargetSheet = WB_TargetFichier.Sheets("PARP")
    Set MainSheet = ThisWorkbook.Sheets("Synthèse")
    Set CEL = Split("E4 C9 H6 H7 H9 H10 H11 H12 H13 H14 H15 H16 H18 H19 M9 M11 Q9 Q11 P15 L15 P19 L19")

    For k = 0 To UBound(CEL)

        For j = 2 To 23

            TargetSheet.Range(CEL(k)).Select
            Selection.Copy
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteValues
            MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteFormat

        Next j

    i = i + 1
    Next k

    ChargerFichier = ChargerFichier + 1
    Application.CutCopyMode = False
    WB_TargetFichier.Close savechanges:=False

End Function

J'ai peu d'expérience en VBA pour comprendre l'origine du problème d'un coup d'oeil mais j'ai l'impression que c'est pas qqchose de très poussée

Quelqu'un saurait-il me donner une indication ?

Merci,

SkillzZ

Le 13/06/2017 à 09:50

Bonjour le fil, bonjour le forum,

Comme tu as déjà spécifié CEL en tant que Range, CEL.Copy suffit. le code correct serait :

For Each CEL In PL
     For j = 2 To 23
         CEL.Copy
          MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteValues
          MainSheet.Cells(j, i).PasteSpecial Paste:=xlPasteFormat
      Next j
      i = i + 1
Next CEL

Évite les Select inutiles, c'est la règle d'or...

Le 13/06/2017 à 09:56

Bonjour ThauThème,

d'accord pour la modification mais tu ne précises plus que je veux copier CEL au sein de la feuille TargetSheet. Donc je pense que c'est problématique. Sachant que:

    Set WB_TargetFichier = Workbooks.Open(NomFichier)
    Set TargetSheet = WB_TargetFichier.Sheets("PARP")
    Set MainSheet = ThisWorkbook.Sheets("Synthèse")
    Set PL = Application.Union(Range("E4"), Range("C9"), Range("H6:H7"), Range("H9:H16"), Range("H18:H19"), Range("M9"), Range("M11"), Range("Q9"), Range("Q11"), Range("P15"), Range("L15"), Range("P19"), Range("L19"))

Je ne travaille pas dans unique classeur. Donc comment spécifié que je veux copier CEL dans cette feuille là ?

Je te remercie.

Edit: J'ai quand même essayé ta solution sans inquer la feuille en question et j'obtiens un "la méthode PasteSpecial de la classe range a échoué"

Le 13/06/2017 à 10:14

Re,

Il serait bon que tu envoies le code complet d'entrée !...

En fait il n'y aura aucun problème si tu définis la plage PL avec le nom de l'onglet de référence à chaque fois :

Set PL = Application.Union(TargetSheet.Range("E4"), TargetSheet.Range("C9"), TargetSheet.Range("H6:H7"), TargetSheet.Range("H9:H16"), TargetSheet.Range("H18:H19"), TargetSheet.Range("M9"), TargetSheet.Range("M11"), TargetSheet.Range("Q9"), TargetSheet.Range("Q11"), TargetSheet.Range("P15"), TargetSheet.Range("L15"), TargetSheet.Range("P19"), TargetSheet.Range("L19"))
Le 13/06/2017 à 10:32

Oui tout à fait, j'ai voulu vous faire gagner du temps en exposant uniquement l'endroit qui me posait problème mais je vous ai finalement fait perdre du temps. Désolé !

Merci beaucoup pour la solution qui fonctionne ! J'ai pas encore construit mes boucles correctement mais je vais pouvoir travailler dessus grâce à ça ! Merci ThauThème !

SkillzZ

Le 13/06/2017 à 11:00

bonjour

pour ton split ses un problème syntaxe

Set Cel = Split("E4", "C9", "H6", "H7", "H9", "H10", "H11", "H12", "H13", "H14", "H15", "H16", "H18", "H19", "M9", "M11", "Q9", "Q11", "P15", "L15", "P19", "L19")
   For k = 0 To UBound(Cel)
      For j = 2 To 23
         TargetSheet.Range(Cel(k)).Copy
         MainSheet.Cells(j, i).PasteSpecial xlPasteValues
         MainSheet.Cells(j, i).PasteSpecial xlPasteFormat
      Next j
   i = i + 1
   Next k

A+

Maurice

Le 13/06/2017 à 13:29

Bonjour, Bonjour ThauThème...

Très bien si tu avances mais il est bon de revenir sur les erreurs, c'est cela qui fait progresser !

L'erreur sur l'utilisation du code de ThauThème aurait dû te sauter aux yeux comme le nez au milieu de la figure !!

Lorsque tu utilises une boucle For Each... Next, tu parcours une collection, la syntaxe est de la forme :

For Each objet In collection

Dans laquelle objet est une variable objet du même type que les objets de la collection parcourue (avec une petite particularité pour l'objet Range qui est à la fois objet et collection : en tant que collection tu peux en parcourir des subdivisions diverses, cellules, lignes, colonnes, aires... qui toutes sont des objets Range). Quand tu ne précises pas :

For Each cel In Range(xxx)
'est équivalent à
For Each cel In Range(xxx).Cells

(mais tu pourras rencontrer quelques cas où Excel n'identifie pas spontanément qu'il s'agit de cellules et où tu devras le préciser...)

Dans le code ci-dessus, cel est une variable de type Range représentant à chaque tour un objet Range-cellule unique appartenant à Range(xxx), objet Range-collection de cellules.

Tu ne peux donc pas l'utiliser comme index pour pointer un objet de la collection avec Range(cel) dans laquelle cel devrait alors être une variable String indiquant une référence de plage ou un nom de plage.

Et encore moins mettre un nom de variable entre guillemets, car là tu fais alors chercher à VBA et Excel le nom "cel" qu'il sera bien en peine de trouver !

Quand à l'erreur sur mon code, elle aurait dû également te sauter aux yeux, c'est à peine moins grossier !

On forme des tableaux de 2 manières : en utilisant des variables déclarées comme tableaux (en les dimensionnant lors de la déclaration ou non) ou en utilisant des variables de type Variant (tableaux non pré-dimensionnés). Tout autre type de variable ne peu contenir de tableau, or tu déclares Cel comme String. Mais si tu vas lui affecter un tableau en utilisant les fonctions Split ou Array, elle ne pourra jamais l'accueillir.

Cordialement.

Rechercher des sujets similaires à "vba creer liste parcourir"