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

Darren Smith

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

MARK858

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
The workbooks & manual code are below

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



Mt
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
The workbook & manual code below


 

MARK858

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

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,977
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Rich (BB code):
 JCM.Range("O" & x).Value = Application.WorksheetFunction.VLookup(JCM.Range("D" & x).Value, SrcDataRange, 12, False)
 
Last edited:

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
Sorry I see I altered the code but still no luck with the VLookup.
Column D in Worksheet JCM has the part code?
 
Last edited:

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
This Msgbox shows empty
MsgBox JCM.Range("O" & x).Value
 

Watch MrExcel Video

Forum statistics

Threads
1,127,539
Messages
5,625,402
Members
416,100
Latest member
lirongr1996

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