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

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
The code below has this code saying = Nothing?
VBA Code:
 Set PLDataRange = PL.Range("A13:P" & LastRow)
??

VBA Code:
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
This below says Empty and highlights Yellow
VBA Code:
 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

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
I`ve simplified the code down
This line below does not find the range in the speadsheet ??

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

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,959
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Put
VBA Code:
MsgBox SrcDataRange.Address
At the end of the the code and run it, what does the message box say?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Done what you said:
And sorry it is finding the range but why is the VLookup not working??
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,959
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
I have now changed the SrcDataRange to 16 columns but the VLookup is still not working
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,959
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Any chance I could send the workbook to you via this forum using the Dropbox?
Sorry still learning the ropes??
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,990
Members
415,872
Latest member
ReignEternal

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