Vba Vlookup to reference range from a file whose name varies

[guilhermelemos]

Hi,


I'm trying to do a vlooup where the lookup range is on a file whose name varies depending on when the report being run was run the last time.


I've figured out a way to find the appropriate file and open it.


What I haven't found is the right way to do the vlooup, so I get the comments from the previous report instance. I'm getting error 438: "Object doesn't support this property or method".


How do I fix it? Any ideas? This will be placed into a larger code, which already works.


Thanks in advance.

Guil.




Code:








Sub Second_vlookup()


ThisWorkbook.UpdateLinks = xlUpdateLinksNever
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wrkMyWorkBook As Workbook
Dim x, xlastrow As Integer
Dim alookup, rlookup, slookup, tlookup, ulookup As Variant

Set wrkMyWorkBook = Workbooks.Open(Filename:=Sheets("Sheet1").Range("C3").Value & "" & Sheets("Sheet1").Range("D3").Value)

Workbooks("Quality_release_priorities_20180202.xlsm").Activate

xlastrow = Cells(Rows.Count, "C").End(xlUp).Row

For x = xlastrow To 2 Step -1


'This is the part that doesn't work.
ActiveSheet.Cells(x, 18).Value = _
Application.WorksheetFunction.VLookup(Cells(x, 1), Workbooks(wrkMyWorkBook(Sheets("Release_Priorities"))).Range("A1:U10000"), 18, False)



Next x


End Sub

 

[Takae]

On Error Resume Next
ActiveSheet.Cells(x, 18).Value = _
  Application.WorksheetFunction.VLookup(cells(x, 1),[COLOR=#ff0000] wrkMyWorkBook[/COLOR].Sheets("Release_Priorities").Range("A1:U10000"), 18, False)
On Error GoTo 0

rough...?

 

[guilhermelemos]

Thanks Takae, I might have tried something like that, but I'll try again when I'm back in the office tomorrow