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
– K.Dᴀᴠɪs, Commented Apr 12, 2018 at 1:36
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
– K.Dᴀᴠɪs, Commented Apr 12, 2018 at 1:39
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
– ashleedawg, Commented Apr 12, 2018 at 2:06

Kajkrow · Accepted Answer · 2018-04-25 06:34:37Z

1

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

edited Apr 25, 2018 at 6:34

answered Apr 12, 2018 at 6:06

Kajkrow

3042 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

imjellybrah26 Over a year ago

hey, sorry I was on leave and just managed to have a look at this. I just want to ask why di you include this: Const Table As String = "Sheet1" ?

Kajkrow Over a year ago

The point is that possibly you can have a different name for "Sheet1" in every single excel-file. so you have to make sure, that all files have the same sheet-name. I normally do this myself to point out that I definetly need this. This is just personnel preference, if you don't like it, you can replace it. I comment the code a lil, to make it easier to understand.

imjellybrah26 Over a year ago

hey thanks for the reply! Unfortunately, my sheet names are not the same - the system generated .csv files make them have the same names as the file name, meaning that I need to add an array function into the equation. since there is only one worksheet per file/workbook, can I just do without that line, so I don't need to add a second arrayt and make the function even more confusing?

Kajkrow Over a year ago

Sry for the delayed reply. I was quite busy. I am gonna edit my post for your case. - if your sheet names really are the same as your file names it shouldnt be too hard bc you can simply take the same array.

Kajkrow Over a year ago

I didn't test it, but it should work. In case it works don't forget to mark your question as answered. :)

|

LFB · Accepted Answer · 2018-04-12 03:00:59Z

1

If you wish to ignore the pop-ups you could use: Application.DisplayAlerts = False at the beginning of your code. Don't forget to reverse it later by putting Application.DisplayAlerts = True at the end of your code.

For the second issue, by scanning your code I realized you used FilePath inside of the string in:

Else

Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],FilePath!R3C2:R8C3,2,FALSE),0)"

End If

Next

It should suffice to try:

Range("C14:C19").Offset(i * 6, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]," & FilePath & "!R3C2:R8C3,2,FALSE),0)"

Also, for the Application.ScreenUpdating = False statement to properly speed up the execution it should be at the beginning of your code.

edited Apr 12, 2018 at 3:00

answered Apr 12, 2018 at 2:02

LFB

6962 gold badges8 silver badges21 bronze badges

4 Comments

imjellybrah26 Over a year ago

hey, thanks for your input. After I keyed in the changes, the code refuses to run, claiming that Next without For

LFB Over a year ago

Apparently there is an End If missing. Write it above the Next statement. I will correct my code also

imjellybrah26 Over a year ago

Hi, the problem still exists :/

LFB Over a year ago

What error message appears now? There is a new answer in the post. Did it work?

Collectives™ on Stack Overflow

Excel VBA Array +vlookup

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related