Can't seem to get the ranges to work

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
This code has 2 sets of ranges both don't seem to work? The SrcDataRange & DesDataRange
One is Destination workbook other is Source workbook?

VBA Code:
Sub Job_Record_Detail()

Application.ScreenUpdating = False
 


    Dim Src As Workbook
    Dim SrcOpen As Workbook
    Dim Des As Workbook
    Dim JCM As Worksheet
    Dim TGSR As Worksheet
    Dim FilePath As String
    Dim Filename As String
    Dim DesDataRange As Range
    Dim SrcDataRange As Range
 

    FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB BOOK\"
    Filename = "JOB RECORD SHEET.xlsm"
 
    Set Des = Workbooks("Automated Cardworker.xlsm")
    Set JCM = Worksheets("Job Card Master")
    Set SrcOpen = Workbooks.Open(FilePath & Filename)
    Set Src = Workbooks("JOB RECORD SHEET.xlsm")
    Set TGSR = Worksheets("TGS JOB RECORD")
 
    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A2:NA").Select
 
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A2:Q").Select

    DesDataRange("A4").Value = Application.WorksheetFunction.VLookup(DesDataRange("G4"), Range("SrcDataRange"), 40, 0)
    Columns("A:Q").EntireColumn.AutoFit
    Range("A4").Select


         
    Src.Close
         
     Application.ScreenUpdating = True
         
         
 
    End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Two things about your range declarations:

1. Get rid of the ".Select" at the end of your range declarations.
You are just defining them here, not selecting them.

2. If you are including starting row, you need an ending row too.
Otherwise, choose the whole column.

So change:
VBA Code:
    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A2:NA").Select
   
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A2:Q").Select
to something like this:
VBA Code:
    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A:NA")
   
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A:Q")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Further to has already been said it should be
VBA Code:
    Set Des = Workbooks("Automated Cardworker.xlsm")
    Set JCM = Des.Worksheets("Job Card Master")
    Set SrcOpen = Workbooks.Open(FilePath & Filename)
    Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
 
    Set SrcDataRange = TGSR.Range("A2:NA??")
 
    Set DesDataRange = JCM.Range("A2:Q??")
 

MARK858

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

ADVERTISEMENT

As a guess...

VBA Code:
With Workbooks(Src).Worksheets(TGSR)
   Set SrcDataRange = .Range("A2:NA" & .Columns("A:NA").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
End With

With Workbooks(Des).Worksheets(JCM)
    Set DesDataRange = .Range("A2:Q" & .Columns("A:Q").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
End With
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your help everyone

This Vlookup in the same code is not working please help,

VBA Code:
DesDataRange("A4").Value = Application.WorksheetFunction.VLookup(DesDataRange("G4"), Range("SrcDataRange"), 40, 0)

Thanks in advance
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Darren

Can you explain, in words, what you are trying to do?

In this code Range("SrcDataRange") is definitely wrong, it should be SrcDataRange, but It's not really clear what you are trying to refer to with DesDataRange("A4") and DesDataRange("G4").

As it is they would refer to the 1st column and 4th row of the range DesDataRange and the 7th column and 4th row of the range DesDataRange respectively.
 

Darren Smith

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

Can you explain, in words, what you are trying to do?

In this code Range("SrcDataRange") is definitely wrong, it should be SrcDataRange, but It's not really clear what you are trying to refer to with DesDataRange("A4") and DesDataRange("G4"). Sorry should of been ("G2")
As it is they would refer to the 1st column and 4th row of the range DesDataRange and the 7th column and 4th row of the range DesDataRange respectively.
I am trying to take value in Destination Workbook Sheet Job Card Master in cell G2 the find corresponding value in the A column in the Source workbook Sheet TGS JOB RECORD then vlookup value in the NV Column.
Then add value from Source Workbook Worksheet to Cell A4 in Destination Workbook Sheet Job Card Master.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Are you trying to return the value from column NV?
You data range only goes up to NA & col 40 in your vlookup is column AN?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,622,002
Members
415,873
Latest member
fuulhouse

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