VBA Vlookup Function from Multiple Workbooks

Question

I using a Vlookup function to bring over data from 4 different files into one sheet. I want to place the vlookup results from the 1st file in Column 4, then the results of the 2nd file in Column 6 moving the results 2 columns until the last file.

I got the first file to write to column 4, but when I select multiple files they continue to write to column 4 instead of moving over 2 columns.

Would it be better to use the Find or Match functions because it will always be a one to one lookup? Any help is greatly appreciated. Thanks!

ThisWorkbook:

SelectedBook:

Sub Vlookup_Spread()

Dim FileToOpen As Variant
Dim FileCnt As Byte
Dim SelectedBook As Workbook
Const StartRow As Long = 9
Dim c As Byte
Dim r As Long


Call Entry_Point ' turn of applications

Dim LastRow As Long
 LastRow = Range("B" & Rows.Count).End(xlUp).Row 'source file
   
'Pick the files to import - allow multiselect
 
FileToOpen = Application.GetOpenFilename(Title:="Select DS8A Spread Files to open" _
, FileFilter:="Text Files(*.txt),*txt*", MultiSelect:=True)

ThisWorkbook.Activate

 
 If IsArray(FileToOpen) Then
 
 For FileCnt = 1 To UBound(FileToOpen)
  Set SelectedBook = Workbooks.Open(Filename:=FileToOpen(FileCnt))
 
  ThisWorkbook.Activate
     
        c = 4
        For r = StartRow To LastRow
            DS8A.Cells(r, c).Value = Application.WorksheetFunction.Vlookup(Range("B" & r).Value, _
            SelectedBook.Sheets(1).Range("A:D"), 3, 0)
          
        Next r
        
        SelectedBook.Close
   Next FileCnt
  
   End If 'is array
       
Call Exit_Point ' turn on applications

End Sub

Answer 1

You are assigning your column number as c=4 inside of the For loop that opens all of your workbooks. Each time you go through the loop, you reset your column to 4, and you never change it, so it will always be 4.

Move the c=4 outside your loop, so it is only assigned once. Then, inside your loop, each time you finish the loop, increment the column number by 2. On the next loop, c will equal 6, then 8, etc.

I haven't tested this but try these 2 changes:

 c = 4  '<---Moved this line outside of your loop
 For FileCnt = 1 To UBound(FileToOpen)
  Set SelectedBook = Workbooks.Open(Filename:=FileToOpen(FileCnt))
 
  ThisWorkbook.Activate
     
        
        For r = StartRow To LastRow
            DS8A.Cells(r, c).Value = Application.WorksheetFunction.VLookup(Range("B" & r).Value, _
            SelectedBook.Sheets(1).Range("A:D"), 3, 0)
          
        Next r
        
        SelectedBook.Close
        c = c + 2   '<---Added this line to increment your column number by 2
   Next FileCnt