List the names of folders horizontally with a loop

Question

I need to list the names of folders horizontally.

Sub 2()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim i As Integer
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("C:\Users\Betty\AppData\Roaming\MetaQuotes\Terminal\B4D9BCD10BE9B5248AFCB2BE2411BA10\MQL4\Files\Export_History")
    i = 1

    For Each objSubFolder In objFolder.subfolders
        Cells(1, i + 1) = objSubFolder.Name
    Next objSubFolder

End Sub

I get this result:

Stepping into the code with the debugger, I see that it overwrites each file name in the same cell.

You've set variable i outside your for loop, then just add 1 for each iteration. The result is 2 for each iteration. Suggest to set i to zero then inside the for say i=i+1, and remove the +1 from Cells. btw, I really like your backward R — Jan, Feb 24 '18 at 14:48

QHarr · Accepted Answer · 2018-02-24T14:49:52.507

1

Try to increment i inside the loop to avoid overwriting

For Each objSubFolder In objFolder.subfolders
    Cells(1, i + 1) = objSubFolder.Name
    i = i + 1
Next objSubFolder

This might be simpler to start i at 2 and remove the initial + 1 as I suspect you actually want to go one cell at a time

i = 2

For Each objSubFolder In objFolder.subfolders
    Cells(1, i ) = objSubFolder.Name
    i = i + 1
Next objSubFolder

edited Feb 24 '18 at 14:49

answered Feb 24 '18 at 14:45

QHarr

83,427
12
54
101

Obviously a rookie mistake. Thank you. Will mark it as an answer in 10 minutes. – I. Я. Newb Feb 24 '18 at 14:49
1

thank you. Probably worth mentioning you should reference the sheet explicitly and not just the ActiveSheet implicitly with Cells as you might hit problems if you are not in the expected sheet. – QHarr Feb 24 '18 at 14:51
Thanks for the advise. I am running each chunk of code to a separate workbook, because the whole code of the general file is quite long. I will reference the sheet when I transfer this chunk to the general code. – I. Я. Newb Feb 24 '18 at 15:02
Thanks! By the way... I have another issue and I wondered if you could take a look at it - https://stackoverflow.com/questions/48940448/proper-loop-trough-sub-folders-in-a-directory-and-import-of-specified-columns-fr . – I. Я. Newb Feb 24 '18 at 15:06

List the names of folders horizontally with a loop

1 Answers1