excel - 我怎样才能让我的循环移动到下一个工作簿而不在第一个工作簿中重复？

Question

我只想用公式插入 3 行，但这些行在第一个打开的工作簿中一遍又一遍地插入。

为了讨论，使用 For Each 循环会更好吗？

Sub FXBuy()

Dim wb As Workbook

Dim PathName$, FileName$

    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    FileName = Dir(PathName)
    Do While FileName <> ""
    Set wb = Workbooks.Open(PathName & FileName)

wb.Sheets("FX").Columns("W:X").Hidden = True
wb.Sheets("FX").Columns("AD:AI").Hidden = True

wb.Sheets("FX").Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 

LastRow = wb.Sheets("FX").Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + wb.Sheets("FX").Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

wb.Sheets("FX").Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

wb.Sheets("FX").Range("S" & LastRow) = 20
wb.Sheets("FX").Range("S" & LastRow + 1) = 50

wb.Sheets("FX").Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

wb.Sheets("FX").Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

wb.Sheets("FX").Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
wb.Sheets("FX").Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
wb.Sheets("FX").Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"

Loop

End Sub

标签： excelvbawhile-loop

Answer 1

您的代码将从With wb.Sheets("FX")语句中受益匪浅，因此您无需重复 100 次。

您的问题是您需要FileName = Dir()在 Loop 之前正确移动到下一个文件。

Public Sub FXBuy()
    Dim PathName As String
    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    
    Dim FileName As String
    FileName = Dir(PathName)
    
    Do While FileName <> vbNullString
        Dim wb As Workbook
        Set wb = Workbooks.Open(PathName & FileName)

        With wb.Worksheets("FX")  ' this is used by every statement that starts with a dot
            .Columns("W:X").Hidden = True
            .Columns("AD:AI").Hidden = True

            .Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 
            LastRow = .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

            .Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

            .Range("S" & LastRow) = 20
            .Range("S" & LastRow + 1) = 50

            .Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
            .Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
            .Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"
        End With
            
        FileName = Dir() ' move to next file
    Loop
End Sub

如果不同的工作簿有不同的工作表，您可以执行以下操作：

请注意，工作簿名称必须包含文件扩展名！

Public Sub FXBuy()
    Dim PathName As String
    PathName = "H:\BASEL Reporting - Oliver's Mock\FX\"
    
    Dim FileName As String
    FileName = Dir(PathName)
    
    Do While FileName <> vbNullString
        Dim wb As Workbook
        Set wb = Workbooks.Open(PathName & FileName)
        
        Dim ws As Worksheet
        Select Case wb.Name  ' workbook names must be with file extension
            Case "FX BUY.xslm":
                Set ws = wb.Worksheets("FX BUY")
            
            Case "other workbook.xslm":
                Set ws = wb.Worksheets("other worksheet")

            Case Else:  ' for all other workbooks use this default
                Set ws = wb.Worksheets("FX")
        End Select

        With ws  ' this is used by every statement that starts with a dot
            .Columns("W:X").Hidden = True
            .Columns("AD:AI").Hidden = True

            .Columns("U:AB").SpecialCells(xlCellTypeVisible).Interior.Color = 65535
 
            LastRow = .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Row + .Columns("B").Find("B", LookAt:=xlPart).MergeArea.Rows.Count - 1

            .Range("A" & LastRow & ":A" & LastRow + 2).EntireRow.Insert

            .Range("S" & LastRow) = 20
            .Range("S" & LastRow + 1) = 50

            .Range("U" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.2,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,U$3:U$" & LastRow - 1 & ")"
            .Range("Z" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,Z$3:Z$" & LastRow - 1 & ")"
            .Range("AB" & LastRow + 1).Formula = "=SUMIF($K$3:$K$" & LastRow - 1 & ",0.5,AB$3:AB$" & LastRow - 1 & ")"

            .Range("U" & LastRow + 2).Formula = "=SUM(U" & LastRow & ",U" & LastRow + 1 & ")"
            .Range("Z" & LastRow + 2).Formula = "=SUM(Z" & LastRow & ",Z" & LastRow + 1 & ")"
            .Range("AB" & LastRow + 2).Formula = "=SUM(AB" & LastRow & ",AB" & LastRow + 1 & ")"
        End With
            
        FileName = Dir() ' move to next file
    Loop
End Sub