Excel VBA 数组 +vlookup

debugcn 发表于 Dev

imjellybrah26

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim ArrMonth As Variant
ArrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Dim i As Long
Dim FilePath As String

On Error Resume Next
For i = LBound(ArrMonth) To UBound(ArrMonth)
    FilePath = "\\shared_network\Task List\2018 Task List\02.DLP TISR\Statistics\ECM\incident_summary - " & ArrMonth(i) & ".csv"

    If Dir(FilePath) <> "" Then
        Stop
    Else
        Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],FilePath!R3C2:R8C3,2,FALSE),0)"
    End If

Next i

Application.ScreenUpdating = False

End Sub

我上面有这段代码，我想用来：

定义一个数组
让 VBA 查看特定文件夹以查找文件。
如果文件不存在，请停止。
如果文件存在，则继续（否则）

以下是我在尝试运行代码时注意到的一些问题：

最初，包含此行中的“FilePath” - Range("C14:C19")。Offset(I * 6, 0) 包含数组。但是，会弹出一个窗口，要求我手动选择丢失文件的位置（当它不存在时）。
我尝试将上面的新代码与 vLookup 公式中的“FilePath”行一起使用，但它会问我在哪里打开名为“FilePath”的文件。

如果需要任何说明，请随时发表评论。这行代码杀了我。

凯克罗夫

经过一些尝试，我得到了这个：

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False 'Deactivate ScreenUpdating for better performance
Application.DisplayAlerts = False 'Deactivate DisplayAlerts to prevent the pop-up of VLOOKUP
Dim ArrMonth As Variant 'Create Array of the months, alternatives possible
ArrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
Dim i As Integer 'Counting var
Const FilePath As String = "\\shared_network\Task List\2018 Task List\02.DLP TISR\Statistics\ECM\" 'Dir to the Files; make sure the "\" is at the end
Const Table As String = "Sheet1" 'Remember: Every sheet needs the same name
On Error Resume Next 'Let's assume there are only errors that we can ignore
For i = LBound(ArrMonth) To UBound(ArrMonth) 'Circle through every single month
    If Dir(FilePath & "incident_summary - " & ArrMonth(i) & ".csv") = "" Then 'If the Dir is non-existened, Dir(...) returns not null, but "", so if "" occurs, this path doesn't exist
        Stop 'Stop right here if Dir non-existening
    Else
        Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'" & FilePath & "[incident_summary - " & ArrMonth(i) & ".csv]" & Table & "'!R3C2:R8C3,2,FALSE),0)" 'Do what I want you to do
    End If
Next i
Application.ScreenRefresh 'Refresh the Screen to update the diffrences
Application.ScreenUpdating = True 'Reactivate ScreenUpdating, bc it won't activate itself
Application.DisplayAlerts = True 'Reactivate DisplayAlerts, bc it won't reactivate itself

End Sub

获得更多信息后，这应该有效：

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False 'Deactivate ScreenUpdating for better performance
Application.DisplayAlerts = False 'Deactivate DisplayAlerts to prevent the pop-up of VLOOKUP
Dim ArrMonth As Variant 'Create Array of the months, alternatives possible
ArrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
Dim i As Integer 'Counting var
Const FilePath As String = "\\shared_network\Task List\2018 Task List\02.DLP TISR\Statistics\ECM\" 'Dir to the Files; make sure the "\" is at the end
'Leave this one out Const Table As String = "Sheet1" 'Remember: Every sheet needs the same name
On Error Resume Next 'Let's assume there are only errors that we can ignore
For i = LBound(ArrMonth) To UBound(ArrMonth) 'Circle through every single month
    If Dir(FilePath & "incident_summary - " & ArrMonth(i) & ".csv") = "" Then 'If the Dir is non-existened, Dir(...) returns not null, but "", so if "" occurs, this path doesn't exist
        Stop 'Stop right here if Dir non-existening
    Else
        'Replace This:
        'Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'" & FilePath & "[incident_summary - " & ArrMonth(i) & ".csv]" & Table & "'!R3C2:R8C3,2,FALSE),0)" 'Do what I want you to do
        'By this:
        Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'" & FilePath & "[incident_summary - " & ArrMonth(i) & ".csv]" & ArrMonth(i) & "'!R3C2:R8C3,2,FALSE),0)" 'Do what I want you to do
    End If
Next i
Application.ScreenRefresh 'Refresh the Screen to update the diffrences
Application.ScreenUpdating = True 'Reactivate ScreenUpdating, bc it won't activate itself
Application.DisplayAlerts = True 'Reactivate DisplayAlerts, bc it won't reactivate itself

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。