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

 

[Darren Smith]

in your main query its pldatarange rather then one you just posted containing srcdatarange

Sorry it should of been Src I did alter this yesterday

 

[Darren Smith]

Depends if the value is there but I am looking at where the OP is stating the error is now.

The code is from Range E2 to O last row. Sorry the first code was wrong

 

[Darren Smith]

This below says Empty and highlights Yellow

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

The errors say
ControlSource = "Could not set the ControlSource property. Member not found"
RowSource = "Could not set the ControlSource property. Member not found"
Row SourceType = "Member not Found"

 

[Darren Smith]

I`ve simplified the code down
This line below does not find the range in the speadsheet ??

Set SrcDataRange = Src.Range("E2:P" & SrcLastRow)

Private Sub Up_Date_Prices_Click()

        Dim SrcOpen As Workbook
        Dim Src As Worksheet
        Dim FilePath As String
        Dim Filename As String
        Dim SrcDataRange As Range
        Dim SrcLastRow As Long
    
     
        
       
        
        FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\PURCHASING\"
        Filename = "TGS Group Inventory Sheet - Main.xlsx"
    
       
      
        Set SrcOpen = Workbooks.Open(FilePath & Filename)
     
        Set Src = SrcOpen.Worksheets("Part List")
        SrcLastRow = Src.Cells(Src.Rows.Count, "A").End(xlUp).row
        Set SrcDataRange = Src.Range("E2:P" & SrcLastRow)
        
End Sub

 

[MARK858]

Put

MsgBox SrcDataRange.Address

At the end of the the code and run it, what does the message box say?

 

[Darren Smith]

Done what you said:
And sorry it is finding the range but why is the VLookup not working??

 

[MARK858]

I already told you that earlier. you were trying to look at the 16th column when you only had 12 columns in the range, that is not possible.

 

[Darren Smith]

I have now changed the SrcDataRange to 16 columns but the VLookup is still not working

 

[MARK858]

Yes but are you now looking in the correct column? I doubt it if you have just extended the range.
What is the correct formula when you write it manually?

 

[Darren Smith]

Any chance I could send the workbook to you via this forum using the Dropbox?
Sorry still learning the ropes??