CODEWITHSUNDEEP
excelvba
Tester_Y
Tester_Y

Reputation: 377

How to create a string variable in Excel VBA to loop through based on variable name?

Trying to set the variable names in a way that allows looping the code without having to type out an instance of each 'directory'.

The intent of this code is to count how many files are in each directory.

Code I have so far (doesn't work):

Sub CountFiles()

Dim xFolder() As Long
Dim xPath() As Long
Dim xCount() As Long
Dim xFile() As String    
Dim z As Long

xFolder(1) = "\\generic path"
xFolder(2) = "\\generic path2"

For z = 1 To 2    
    xPath(z) = xFolder(z) & "\*.xlsx"
    xFile(z) = Dir(xPath(z))
    Do While xFile(z) <> ""
        xCount(z) = xCount(z) + 1
        xFile(z) = Dir()
    Loop

    With Worksheets("test")
        .Cells(3, 2).value = xCount(z)
    End With
Next z

End Sub

If I don't use the looping method, I can just set the variables to be xFolder1, xFolder2, xFolder3 etc., but then I'd have to run an instance of the code which loops through the directories to count for each iteration.

Is there a way to do this? Thanks.

Upvotes: 0

Views: 1633

Answers (2)

Tester_Y
Tester_Y

Reputation: 377

Thanks for the answers and explanations surrounding types/arrays. Added the y variable to advance the output columns by 1 each time.

Final working code:

Sub CountFiles()

Dim xFolder(1 To 2) As String
Dim xPath(1 To 2) As String 
Dim xCount(1 To 2) As Long
Dim xFile(1 To 2) As String    
Dim z As Long
Dim y As Long

xFolder(1) = "\\generic path"
xFolder(2) = "\\generic path2"

For z = 1 To 2    
    xPath(z) = xFolder(z) & "\*.xlsx"
    xFile(z) = Dir(xPath(z))
    Do While xFile(z) <> ""
        xCount(z) = xCount(z) + 1
        xFile(z) = Dir()
    Loop

y = z + 1

    With Worksheets("test")
        .Cells(3, y).value = xCount(z)
    End With

y = 0

Next z

End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33662

Define all the following as String array, in your case you want each array to have 2 elements.

Dim xFolder(1 To 2) As String
Dim xPath(1 To 2) As String
Dim xCount(1 To 2) As Long
Dim xFile(1 To 2) As String

Upvotes: 3

Related Questions