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

Darren Smith

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

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
can you do debug.print for the variables srclastrow & deslastrow and share the output if it matches your data table
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
392
Office Version
  1. 2019
Platform
  1. Windows
The below are right

ScrDataRange = 1238

DesDataRange = 182
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,542
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The code below says Error = Nothing
VBA Code:
Set SrcDataRange = Src.Range("E2:P" & SrcLastRow)
No, it doesn't. That code doesn't say anything, it just assigns a range to a variable. Are you getting an error message? I assume so, so what does the error message actually say, and which line does the debugger highlight?
 

Darren Smith

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

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

try replacing this line of code and check
provided the d15 value is a number
VBA Code:
 JCM.Range("O15").Value = Application.WorksheetFunction.VLookup(cdbl(JCM.Range("D15").value), PLDataRange, 16, 0)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,542
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Empty is not an error message.
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
in your main query its pldatarange rather then one you just posted containing srcdatarange
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
SrcDataRange is columns E:P which is only 12 columns but you are trying to lookup the 16th column?
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
in the main query (initial), it was written A:P then it would have worked i suppose ?
SrcDataRange is columns E:P which is only 12 columns but you are trying to lookup the 16th column?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
in the main query (initial), it was written A:P then it would have worked i suppose ?
Depends if the value is there but I am looking at where the OP is stating the error is now.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,140
Messages
5,628,937
Members
416,354
Latest member
JojoMaque

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