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

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
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]
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
I`ve changed it to what you`ve said but it`s still is not working?
Can you see any more mistakes??
 
Last edited:
Upvote 0
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:
VBA Code:
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:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top