Vlookup in vba

[CamVilla]

Hi, I am new with the VBA, can someone help me, I have made this macro, but the loop across files in a directory runs only in cell G13, and I would like that, once it detects that the value is in a file, pastes it, and continues with the next cell. In other words, my macro result is the value of the last file that appears.

Sub Vlookup()
Dim MyFolder As String, MyFile As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
On Error Resume Next
MyFolder = .SelectedItems(1)
Err.Clear
On Error GoTo 0
End With

If MyFolder = "" Then End

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False

Workbooks.Open Filename:="C....xlsm"
Sheets("BASE").Select
Range("G13").Select
'Range("G13:M21").Formula = "=VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)"
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)"

Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir

Loop
'
' 'turns settings back on that you turned off before looping folders
'
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationManual

End Sub

 

[mart37]

FirstCol = 7
LastColBase = 7
MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
LastColMyFile = Workbooks(MyFile).Worksheets("Hoja1").Cells(12, Columns.Count).End(xlToLeft).Column
'orkbooks.Open Filename:="C:\Users\Usuario\Documents\Datos\26.02.2021_BASE.xlsm"
Sheets("BASE").Select
LastRowBase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(13, LastColBase), Cells(LastRowBase, LastColBase + (LastColMyFile - 7))).Formula = "=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+column()-" & LastColBase - 7 & ",FALSE)"
LastColBase = LastColBase + (LastColMyFile - 6)
Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir
Loop

 

[CamVilla]

FirstCol = 7
LastColBase = 7
MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
LastColMyFile = Workbooks(MyFile).Worksheets("Hoja1").Cells(12, Columns.Count).End(xlToLeft).Column
'orkbooks.Open Filename:="C:\Users\Usuario\Documents\Datos\26.02.2021_BASE.xlsm"
Sheets("BASE").Select
LastRowBase = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(13, LastColBase), Cells(LastRowBase, LastColBase + (LastColMyFile - 7))).Formula = "=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+column()-" & LastColBase - 7 & ",FALSE)"
LastColBase = LastColBase + (LastColMyFile - 6)
Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir
Loop


[/QUOTE]
Works perfect! Thank you so much