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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
362
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]
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
362
Office Version
  1. 2019
Platform
  1. Windows
I`ve changed it to what you`ve said but it`s still is not working?
Can you see any more mistakes??
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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.
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
362
Office Version
  1. 2019
Platform
  1. Windows
I`ve done what you said now it says error = 2024
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,007
Members
416,004
Latest member
reitz1

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
Top