Trying to open workbook then use VLookup to find data then close workbook

[Darren Smith]

The code below opens workbook but returns #N/A or #Ref! in column No. 16



Private Sub Up_Date_Prices_Click()



Application.ScreenUpdating = False

Dim SrcOpen As Workbook
Dim Des As Workbook
Dim JCM As Worksheet
Dim PL As Worksheet
Dim FilePath As String
Dim Filename As String
Dim DesDataRange As Range
Dim SrcDataRange As Range
Dim LastRow As Long
Dim ComBox As Object


FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\PURCHASING\"
Filename = "TGS Group Inventory Sheet - Main.xlsx"



Set SrcOpen = Workbooks.Open(FilePath & Filename)
Set PL = SrcOpen.Worksheets("Part List")
LastRow = PL.Cells(PL.Rows.Count, "E").End(xlUp).row
Set SrcDataRange = PL.Range("E13:O" & LastRow)
Windows("TGS Group Inventory Sheet - Main.xlsx").Visible = False

Set Des = Workbooks("Automated Cardworker.xlsm")
Set JCM = Des.Worksheets("Job Card Master")
Set DesDataRange = JCM.Range("D13:O299")
Set ComBox = Me.Jobcard_Demands





JCM.Range("O13:O299").Value = Application.WorksheetFunction.VLookup(JCM.Range("D13:D299"), SrcDataRange, 16, 0)
Range("O13:O299").Select



Application.DisplayAlerts = False

SrcOpen.Close

Application.DisplayAlerts = True

Application.ScreenUpdating = True


End Sub[/CODE]

 

[Joe4]

For one thing, your SrcDataRange is defined as columns E:O, that is column 5 to column 16. That is a total of 12 columns.
Yet, in your formula, you are trying to return column 16. There is no column 16 in your lookup range. Try changing it to 12.

There could potentially be other issues too, but at first glance, this one sticks out like a sore thumb.

 

[Darren Smith]

I`ve changed it to what you`ve said but it`s still is not working?
Can you see any more mistakes??

 

[Joe4]

I am not convinced you can do them all at once like this, especially having a multi-range reference in the first argument of your VLOOKUP:

JCM.Range("O13:O299").Value = Application.WorksheetFunction.VLookup(JCM.Range("D13:D299"), SrcDataRange, 12, 0)

Try to do it on just the first one, and see if that works:

JCM.Range("O13").Value = Application.WorksheetFunction.VLookup(JCM.Range("D13"), SrcDataRange, 12, 0)

If that does, then we can try going about this in a different manner.

 

[Darren Smith]

I`ve done what you said now it says error = 2024

 

[Joe4]

I think you still need the workbook reference in your range reference.

I suggest you take a different approach to this.
Turn on your Macro Recorder, and simply record yourself entering the VLOOKUP formula in cell O13.
Once you get it to work, and return the correct value, then you can stop the Macro Recorder.
Then we can take that code, and put in your VBA code, after cleaning it up a little.

My suggestion would be to first populate O13:O299 with the VLOOKUP formulas.
Then, changed that whole range to a value afterwards in your code.