specific file types in specific folders with vba

Question

I have several folders that I want this code to edit inbetween start and finish. The part of the code that recycles the search in a different folder doesn't see any *.csv when Folder2 replaces Folder1 for SubFolder, but if the initial condition for SubFolder at the start is changed manually by me from "Folder1" to "Folder2" it will detect *.csv files in that folder now. It also detects *.csv in "Folder1" and "Folder3" when they are the initial condition for SubFolder. I did check as recommended in other questions and couldn't find any missing "\" in this code

Global Myfile, MyFolder, NewFile, SubFolder As String

Sub SpecificFileTypeInSpecificFolders()
'
SubFolder = "Folder1"
MyFolder = "C:\xxxxxx\" & SubFolder
Myfile = Dir(MyFolder & "\*.csv")
MsgBox SubFolder
MsgBox Myfile
Do While Myfile <> ""
MsgBox SubFolder
MsgBox Myfile
Myfile = Dir
    If Myfile = "" Then
        If SubFolder = "Folder2" Then 'several more folders like this
            SubFolder = "Folder3"
        End If
        If SubFolder = "Folder1" Then
            SubFolder = "Folder2"
        End If
    End If
    MsgBox SubFolder
    MsgBox Myfile
    Loop
End Sub

Answer 1

Changing the value of the SubFolder variable after the line of code MyFolder = "C:\xxxxxx\" & SubFolder has no effect on the MyFolder variable. When you concatenate string variables using the & operator you are taking the value of the SubFolder variable and appending it to the C:\xxxxxx\ text and putting the result into the value of the MyFolder variable.

If you want to search a known list of folders, create an array of the folders and then loop through the array.

Avoid using Globals - best practice is to declare variables with the minimum scope required. In VBA when you declare several variables on the same line you must specify the variable type of each one because otherwise they get defined as Variant. So in your line of code only SubFolder is defined as a string:

Global Myfile, MyFolder, NewFile, SubFolder As String

Instead use:

Dim Myfile As String, MyFolder As String, NewFile As String, SubFolder As String

Personally, I prefer to keep each variable declaration on a separate line.

This code should run ok:

Sub SpecificFileTypeInSpecificFolders()
'
Dim myFile As String
Dim subFolder As Variant ' Must be variant to enable looping through the array
Dim folderNames As Variant
Dim mainFolder As String

    folderNames = Array("Folder1", "Folder2", "Folder3")
    mainFolder = "C:\xxxxxx\"

    For Each subFolder In folderNames
        MsgBox subFolder
        myFile = Dir(mainFolder & subFolder & "\*.csv")
        Do While myFile <> ""
            MsgBox myFile
            myFile = Dir
        Loop
    Next subFolder

End Sub