时间戳

首页 > 解决方案 > FOR EACH 带有单词的循环

excel - FOR EACH 带有单词的循环

问题描述

如何使用单词来应用 FOR EACH 循环？假设我有 96 个单独的 csv 文件，如下所示，并且需要更改每个文件的第一个单元格。这些文件是

c:\2010\2010_jan_apple.csv
c:\2010\2010_jan_orange.csv
c:\2010\2010_feb_apple.csv
c:\2010\2010_feb_orange.csv
...  
c:\2010\2010_dec_apple.csv
c:\2010\2010_dec_orange.csv
c:\2012\2011_jan_apple.csv
c:\2012\2011_jan_orange.csv
...  
c:\2016\2016_dec_apple.csv
c:\2016\2016_dec_orange.csv

所以我认为会有一个 FOR 循环和两个 FOR EACH 循环。

year=2010,2012,2014,2016  
month=jan,feb,...,dec  
type=apple,orange

我的愚蠢代码是

sub a()
application.displayalerts=false
for year=2010 to 2016 step 2
workbooks.open "c:\" & year & "\" & year & "_jan_apple.csv"
cells(1,1)="apple"
with activeworkbook
.saveas
.close
end with
workbooks.open "c:\" & year & "\" & year & "_jan_orange.csv"
cells(1,1)="orange"
with activeworkbook
.saveas
.close
end with
...
next year
end sub

在 VBA 中，我可以创建一个诸如 {"jan","feb",...,"dec"} 或 {"apple","orange"} 的列表来使用 FOR EACH 迭代吗？非常感谢。

标签： excelvba

解决方案

如果您只有“Apple and Oranges”，那么您不需要循环。如果你有更多的“水果”，那么是的，你将需要一个循环。此外，您不需要为月份名称提供数组。你可以从MonthName()

这是你正在尝试的吗？

Option Explicit

Sub Sample()
    Dim flName As String
    Dim i As Long
    Dim yr As Long

    For yr = 2010 To 2016 Step 2
        For i = 1 To 12
            flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_apple.csv"
            'UpdateWb flName, "apple"
            Debug.Print flName
            flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_orange.csv"
            'UpdateWb flName, "orange"
            Debug.Print flName
        Next i
    Next yr
End Sub

Sub UpdateWb(f As String, fruit As String)
    On Error GoTo Whoa

    Dim wb As Workbook
    Set wb = Workbooks.Open(flName)
    wb.Sheets(1).Cells(1, 1) = fruit
    Application.DisplayAlerts = False
    wb.Close (True)
    DoEvents
LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

如果你有更多的水果，那么你可以使用第三个循环。例如

Sub Sample()
    Dim flName As String
    Dim fruits() As Variant
    Dim i As Long
    Dim yr As Long
    Dim itm As Variant

    '~~> Add more if required
    fruits = Array("Apple", "Orange", "Guava")

    For yr = 2010 To 2016 Step 2
        For i = 1 To 12
            For Each itm In fruits
                'c:\2010\2010_jan_apple.csv
                flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_" & itm & ".csv"
                'UpdateWb flName, itm
                Debug.Print flName
            Next itm
        Next i
    Next yr
End Sub

推荐阅读