Vlookup macro doesn't work

[Potatokat]

I am trying to write a macro that preforms a vlookup across two open workbooks. I create a column named "risk" in the BJ column of the "exposure" sheet and return my vlookup values from the AK column in my other wookbook (datasource.xlsx) starting in BJ2 and down until there are no more data entries. Nothing happens when I click run with my code below. I'm not sure what I am doing wrong.

Sub VlookupMultipleWorkbooks()



Dim lookFor As Range

Dim srchRange As Range



Dim book1 As Workbook

Dim book2 As Workbook



Dim book2Name As String

book2Name = "datasource.xlsx"



Set book1 = ThisWorkbook



Set book2 = Workbooks(book2Name)



With Worksheets("exposure")

Dim lastRow As Long

Dim i As Long



Range("BJ1").Formula = "risk"



lastRow = .Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

Set lookFor = book1.Sheets("exposure").Cells(i, "AD") ' value to find

Set srchRange = book2.Sheets(1).Range("$A:$AK") 'source

Range("BJ" & i).Formula = Application.VLookup(lookFor, srchRange, 37, False)

Next i

End With



End Sub

I just noticed that my pasted VBA code looks really strange. Here is a screenshot:

 

[HighAndWilder]

When posting code use the Quick wrap selection as VBA code.

It will avoid the problem that you have encountered.

 

[Potatokat]

Solved now!

 

[Potatokat]

Thank you for the advice!

When posting code use the Quick wrap selection as VBA code.

It will avoid the problem that you have encountered.

View attachment 65991

 

[Peter_SSs]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.