对于每个...下一个不循环 [英] For each... Next not looping

问题描述

我有一个宏，该宏使用文件名中的字符串来标识要将文件移动到的文件夹.它会在A列中查找字符串，并使用从B列中相邻单元格中检索到的国家/地区名称来构建文件夹名称.

I have a macro that uses a string in the filename to identify the folder to move the file to. It looks up the string in column A and builds the folder name using the country name retrieved from the adjacent cell in column B.

If语句正确执行(正确移动文件).但是，对于每个 ... Next 循环浏览文件不起作用，我不知道为什么不这样做.

The If statement executes correctly (moves the file correctly). However, the For each... Next looping through the files doesn't work and I cannot see why not.

预先感谢您的帮助.

Sub MoveFiles_SpecificFolders_Loop()

Dim SrepFSO As FileSystemObject
Dim Srep As File
Dim fso As New FileSystemObject
Dim HoldingFolder As String
Dim TargetFolder As String
Dim HldFolder As Folder
Dim i As Integer

HoldingFolder = "C:\Users\xyz\Holding\"
TargetFolder = "C:\Users\xyz\Countries\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set SrepFSO = New Scripting.FileSystemObject
Set HldFolder = SrepFSO.GetFolder(HoldingFolder)

    For Each Srep In HldFolder.Files
    
        For i = 2 To 50
   
        If InStr(Srep, Sheet2.Cells(i, 1)) <> 0 Then
        SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
        Destination:=TargetFolder & Sheet2.Cells(i, 1).Offset(, 1) & "\" & Srep.Name

        End If
        
        Next i
        
    Next Srep

End Sub

推荐答案

文件移动后，您需要退出内部for循环.

You need to exit the inner for loop once the file has been moved.

尝试一下

Sub MoveFiles_SpecificFolders_Loop()

Dim SrepFSO As FileSystemObject
Dim Srep As File
Dim fso As New FileSystemObject
Dim HoldingFolder As String
Dim TargetFolder As String
Dim HldFolder As Folder
Dim i As Integer

HoldingFolder = "C:\Users\xyz\Holding\"
TargetFolder = "C:\Users\xyz\Countries\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set SrepFSO = New Scripting.FileSystemObject
Set HldFolder = SrepFSO.GetFolder(HoldingFolder)

    For Each Srep In HldFolder.Files
    
        For i = 2 To 50
   
        If InStr(Srep, Sheet2.Cells(i, 1)) <> 0 Then
            If Not SrepFSO.FolderExists(TargetFolder & Sheet2.Cells(i, 2)) then
                SrepFSO.CreateFolder TargetFolder & Sheet2.Cells(i, 2)
            End if
            
            SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
            Destination:=TargetFolder & Sheet2.Cells(i, 2) & "\" & Srep.Name
            Exit For
        End If
        
        Next i
        
    Next Srep

End Sub

这篇关于对于每个...下一个不循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！