Can`t work out why my Range on a workbook worksheet says = Nothing

[Darren Smith]

The code below has this code saying = Nothing?

 Set PLDataRange = PL.Range("A13:P" & LastRow)

??

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 PLDataRange As Range
        Dim LastRow As Long

        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, "A").End(xlUp).row
        Set PLDataRange = PL.Range("A13:P" & LastRow)
        Windows("TGS Group Inventory Sheet - Main.xlsx").Visible = True
       
        Set Des = Workbooks("Automated Cardworker.xlsm")
        Set JCM = Des.Worksheets("Job Card Master")
  

        JCM.Range("O15").Value = Application.WorksheetFunction.VLookup(JCM.Range("D15"), PLDataRange, 16, 0)

      
      
      Application.DisplayAlerts = False
      
       SrcOpen.Close
      
      
      Application.DisplayAlerts = True
            
      Application.ScreenUpdating = True


          End Sub

 

[MARK858]

You can put it on dropbox and post the link it provides in the thread but is there any reason that you can't provide a working normal VLookup formula?

 

[Darren Smith]

The workbooks & manual code are below

=VLOOKUP(D15,[TGS Group Inventory Sheet - Main.xlsx]Parts List!$E$2:$P$1500,2,FALSE)

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

Mt

 

[Darren Smith]

The workbook & manual code below

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[MARK858]

So without looking at your workbook why would you expect to get the correct result when your manual formula is looking at the 2nd column (column F) and yet you tried to put a 16 there instead of a 2.

 

[Darren Smith]

Sorry very confused please try to make it work.
On the Destination workbook Automated Cardworker I need prices updated using column D for Part code then column O for updated prices
On the Source workbook TGS Group Inventory Sheet - Main I need to VLookup prices in column P using column E for the Part code
Hope that helps

 

[MARK858]

Main I need to VLookup prices in column P using column E f

Column P would be 12 in your code not 16 and you are also looking up the value in column D not E

 JCM.Range("O" & x).Value = Application.WorksheetFunction.VLookup(JCM.Range("D" & x).Value, SrcDataRange, 12, False)

 

[Darren Smith]

Sorry I see I altered the code but still no luck with the VLookup.
Column D in Worksheet JCM has the part code?

 

[Darren Smith]

This Msgbox shows empty
MsgBox JCM.Range("O" & x).Value