Excel VBA Array +vlookup

Question

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

I've this code above that I want to use to :

Define an Array
Have VBA look inside a specific folder to look for the file.
If the file doesn't exist, Stop.
If the file exists then continue (Else)

And here are a few problems I noticed when I tried to run the code:

Initially, the "FilePath" in the line containing this - Range("C14:C19"). Offset(I * 6, 0) included the array. However, a window will pop-up asking me to manually select the location of the missing file (when it isn't there).
I tried to use this new code above with the "FilePath" line in the vLookup formula but it will ask me where to open the file titled "FilePath".

If any clarifications are needed please feel free to comment. This line of code is killing me.

Is there a particular reason you entered ScreenUpdating = False twice? — K.Dᴀᴠɪs
It's not going to speed it up any more if you enter it once or a thousand times. Not to mention the second occurrence is when the macro is ending, so that really doesn't make any sense. — K.Dᴀᴠɪs
Here's a better way to get the short month name for month I, instead of the array lookup: Format(DateSerial(1, i, 1), "MMM") — ashleedawg

Kajkrow Kajkrow · Accepted Answer · 2018-04-12T06:06:01

After some trys I got this:

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

After having more information this should work:

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

Excel VBA Array +vlookup

2 Answers