excel - 如何添加嵌套的 For/Next 循环来分割文件切割？

问题描述

我有一个简单的脚本，它利用 for/next 循环通过 do 在一列中按值剪切MgrLvl4 = Data(i, 6)文件If Data(i, 1) <> MgrLvl4 Then。它完美地切割了一切，但我还有另一个问题。如果我有另一个元素MgrLvl4可以分为两类，我将如何实现另一个 for/next 循环来获取拆分文件？

示例：Mgrlvl4在 F 列中，它是 Mgr 1。G 列有两个值拆分 Mgr 1 的总体。为方便起见，我们只说 G 列将 Mgr 1 的一半作为市场 1，另一半作为市场 2。

我想放一些类似的东西：If Data(I,6) AND Data(I,7) <> Mgrlvl4 AND Market Then这将确保if F2<>F3 & G2<>G3在开始下一个文件之前看到。

其余代码：

    Option Explicit

    Sub Main()
    Dim Wb As Workbook
    Dim Data, MgrLvl4, Login, Market
    Dim i As Long, j As Long, k As Long, a As Long
    Dim Dest As Range

    Set Wb = Workbooks("Report_Template.xlsx")

    Set Dest = Wb.Sheets("Population").Range("A2")

    With ThisWorkbook.Sheets("Sheet1")
        Data = .Range("G2", .Range("A" & Rows.Count).End(xlUp))
    End With
    Wb.Activate
    Application.ScreenUpdating = False

    For i = 1 To UBound(Data)

        If Data(i, 6) <> MgrLvl4 Then

            If i > 1 Then

                Dest.Select

                Wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
                              ValidFileName(Market & " - " & Login & " - " & MgrLvl4 & " - Report.xlsx")
            End If

            With Sheets("Exempt Population")
                .Rows(2 & ":" & .Rows.Count).ClearContents
            End With

            MgrLvl4 = Data(i, 6)
            Login = Data(i, 3)
            Market = Data(i, 7)

            j = 0
        End If

        a = 0
        For k = 1 To UBound(Data, 2)
            Dest.Offset(j, a) = Data(i, k)
            a = a + 1
        Next

        j = j + 1
    Next

    SaveCopy Wb, Market, Login, MgrLvl4                     '<< save the MgrLvl4 report


End Sub

Sub SaveCopy(Wb As Workbook, Market, Login, MgrLvl4)
    Wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
                  ValidFileName(Market & " - " & Login & " - " & MgrLvl4 & " - Report.xlsx")
End Sub

Private Function ValidFileName(ByVal FName As String, _
                               Optional ByVal ReplaceChar As String = "") As String
    'Return a filename without invalid chars
    'Avoid CON, PRN, AUX, NUL, COM1 to COM9, LPT1 to LPT9 as filename
    Const InvalidChars = "\/:*?""<>|"
    Dim i As Integer, p As Long
    Dim Digit As String
    For i = 1 To Len(InvalidChars)
        Digit = Mid$(InvalidChars, i, 1)
        p = InStr(FName, Digit)
        Do While p > 0
            Mid$(FName, p, 1) = vbNullChar
            p = InStr(FName, Digit)
        Loop
    Next
    For i = 1 To 31
        Digit = Chr$(i)
        p = InStr(FName, Digit)
        Do While p > 0
            Mid$(FName, p, 1) = vbNullChar
            p = InStr(FName, Digit)
        Loop
    Next
    ValidFileName = Replace(FName, vbNullChar, ReplaceChar)
End Function

源数据的工作表示例：

标签： excelvba

excel - 如何添加嵌套的 For/Next 循环来分割文件切割？

问题描述

解决方案

推荐阅读