CODEWITHSUNDEEP
excelvba
jmten
jmten

Reputation: 49

Increment a column in a loop

i'm facing a problem that seems very weird.

i want to loop through all files in a directory to count the number of file (to do a For i=1 to number file) ((OK), to open each one of them (OK) and copy a range to a mastersheet. my problem is that i want that after each file, my paste destination will be deplaced on step on the right (using my loop i). for a reason i don't figure out, it doesnt work and i presume its because i dont place my loop in the proper place but i can't figure out where to place it.

if anyone has any idea, i would be very thankful for the help:) regards,

jean michel 

Sub synthèsewb()

    Dim synthese As Worksheet
    Dim chemin As String
    Dim fichier As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    Dim i As Integer

    ' détermine la feuille de synthèse
    Set synthese = ThisWorkbook.Sheets(1)

    ' chemin d'accès aux fichiers
     chemin = "V:\DFIN\COM\CCG\CONSO\6-Fichiers de contrôle de liasses\2017\traitement par macro\"

    ' appel de tous les fichiers xls
    fichier = Dir(chemin & "*.xls*")

    'Comptage nombre de fichiers existant
    Do Until fichier = ""
        nbfichier = nbfichier + 1
        fichier = Dir
    Loop

    ' boucle sur tous les fichiers
             Do While fichier <> ""

            ' ouvre les fichiers

                 Set WorkBk = Workbooks.Open(chemin & fichier)

            'boucle sur toutes les feuilles du classeur ouvert

                        For Each sh In WorkBk.Worksheets
                            nom = WorkBk.Name
                           For i = 2 To nbfichier
                                 If sh.Name = "copieSynthèse" Then


                                sh.Range("H16:H22").Copy synthese.Cells(3, i)
                                sh.Range("H10").Copy synthese.Cells(10, i)

                                Set alimenter = ThisWorkbook.Sheets(1).Cells(2, i)

                                            For Each c In alimenter.Cells

                                                    If c.Value = "" Then c.Value = nom

                                            Next c


                                End If
                                i = i + 1
                          Next i
                       Next sh



        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        fichier = Dir()
    Loop



    ' Call AutoFit on the destination sheet so that all
    ' data is readable.
    ActiveSheet.Columns.AutoFit
End Sub

Upvotes: 0

Views: 51

Answers (2)

brainac
brainac

Reputation: 410

I don't think you need i loop at all. 

i = 2
Do While fichier <> ""
    ' ouvre les fichiers
    Set WorkBk = Workbooks.Open(chemin & fichier)
    nom = WorkBk.Name
    'boucle sur toutes les feuilles du classeur ouvert
    For Each sh In WorkBk.Worksheets
        If sh.Name = "copieSynthèse" Then
            sh.Range("H16:H22").Copy synthese.Cells(3, i)
            sh.Range("H10").Copy synthese.Cells(10, i)
            Set alimenter = ThisWorkbook.Sheets(1).Cells(2, i)
            If alimenter.Value = "" Then alimenter.Value = nom
        End If
    Next sh
    ' Close the source workbook without saving changes.
    WorkBk.Close savechanges:=False
   ' Use Dir to get the next file name.
    fichier = Dir()
    i = i + 1
Loop

If you meant to shift by 2 columns for each file change to i = i + 1. Note I couldn't test it not having the same files, and it would take too long to reproduce the environment. Also you don't need to iterate over 1x1 range, just refer it directly.

Upvotes: 2

Ahmad
Ahmad

Reputation: 12737

I don't speak French, but I could tell that nbfichier holds the number of the current workbook being opened.

That being said, it does not make any sense to start a loop with i=2 since the expected starting value is 1.

For i = 2 To nbfichier

Upvotes: 0

Related Questions