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

 

[CamVilla]

From cell G13 till first empty column (since the number of columns varies according to workbook)

 

[mart37]

and how many rows?

 

[CamVilla]

and how many rows?

till first empty row, same as columns, number os rows vary depending on workbook

 

[mart37]

And how many rows in BASE?

 

[CamVilla]

And how many rows in BASE?

1004

 

[mart37]

For one workbook:

Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
LastColMyFile = Workbooks(MyFile).Worksheets("Hoja1").Cells(12, Columns.Count).End(xlToLeft).Column
Workbooks.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, 7), Cells(LastRowBase, LastColMyFile)).Formula = "=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)"
Workbooks(MyFile).Close SaveChanges:=False

 

[CamVilla]

thanks

For one workbook:

Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
LastColMyFile = Workbooks(MyFile).Worksheets("Hoja1").Cells(12, Columns.Count).End(xlToLeft).Column
Workbooks.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, 7), Cells(LastRowBase, LastColMyFile)).Formula = "=+VLOOKUP(RC1,[" & MyFile & "]Hoja1!R13C1:R500C50,+COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)"
Workbooks(MyFile).Close SaveChanges:=False

[/CODE
[/QUOTE]

 

[CamVilla]

thanks

But it stucks in the loop

 

[CamVilla]

thanks

When +COLUMN([" & MyFile & "]Hoja1!R[1]C),FALSE)" , can you help me with this, when the file vlookingup changes the column formula has to start again, but in the code it continues, for example,

workbook1 workbook2
=+BUSCARV(....;+COLUMNA('C:....)\[C42.xls]Hoja1'!S14);FALSO) =+BUSCARV(....;+COLUMNA('C:....)\[C42.xls]Hoja1'!T14);FALSO)


workbook1 workbook2
=+BUSCARV(....;+COLUMNA('C:....)\[C42.xls]Hoja1'!S14);FALSO) =+BUSCARV(....;+COLUMNA('C:....)\[C42.xls]Hoja1'!G14);FALSO)

 

[mart37]

You mean for the second file?