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

 

[RoryA]

But it can`t seem to open the Worksheet to Vlookup in

What does that mean? You don't open a worksheet. Are you getting an error of some kind?

When I say it's gone out of scope, I mean that the variable will have been cleared since it was declared and set within that routine. Once the routine ends, the variable doesn't exist any longer.

 

[Darren Smith]

What does that mean? You don't open a worksheet. Are you getting an error of some kind?

When I say it's gone out of scope, I mean that the variable will have been cleared since it was declared and set within that routine. Once the routine ends, the variable doesn't exist any longer.

Because the debug says

Debug Print Src

= Nothing &

Debug Print SrcDataRange

= Nothing
I`ve been working on this earlier so it`s different as you can see below. I`ve added x variable for Rows count and

on error resume next

Private Sub Up_Date_Prices_Click()


    
Application.ScreenUpdating = False
    
        Dim SrcOpen As Workbook
        Dim Des As Workbook
        Dim JCM As Worksheet
        Dim Src As Worksheet
        Dim FilePath As String
        Dim Filename As String
        Dim SrcDataRange As Range
        Dim DesDataRange As Range
        Dim SrcLastRow As Long
        Dim DesLastRow As Long
        Dim x As Long
        
        On Error Resume Next
        
        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").Activate
        Debug.Print Src
        SrcLastRow = Src.Cells(Src.Rows.Count, "A").End(xlUp).row
        SrcDataRange = Src.Range("E2:P" & SrcLastRow)
        Debug.Print SrcDataRange
        
        Windows("TGS Group Inventory Sheet - Main.xlsx").Visible = True
       
        Set Des = Workbooks("Automated Cardworker.xlsm")
        Set JCM = Des.Worksheets("Job Card Master")
        DesLastRow = JCM.Cells(JCM.Rows.Count, "A").End(xlUp).row
        
        For x = 13 To DesLastRow

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


          End Sub

 

[RoryA]

First, and most important, you shouldn't just add On Error Resume Next to your code without good reason, since it will prevent you from discovering what is wrong with your code. You should only use it to suppress expected errors and you should turn it off as soon as possible.

Second, none of those debug statements are valid, so you couldn't possibly be using them - it's Debug.Print, not Debug Print, and you don't apply it to an object, you apply it to a property of an object. Furthermore, neither of them would actually return the word Nothing even if they did work. ('Nothing' has a very specific meaning in VBA, namely that an object type variable doesn't have an object assigned to it)

So, please remove the On Error Resume Next and then tell me if you get an error message and, if so, on which line, and what is the message?

 

[Darren Smith]

I`ve taken Away the debug code and On error resume Next

This code below says SrcDataRange= Nothing

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

 

[RoryA]

You no longer have Set at the start of that line, which you need.

 

[Darren Smith]

This line says Value empty.
Also, the other line above still says = Nothing with Set at the front

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

 

[RoryA]

Have you added the Set?

 

[Darren Smith]

Have you added the Set?

Yes I have

 

[RoryA]

So what is the error message, and where does it occur?

 

[Darren Smith]

The code below says Error = Nothing

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