I need to fill details to another workbook

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
I am trying to fill in details to another workbook but I get error 1004 Unable to get the VLookup property of the worksheet function class.
How can I sort this error?

VBA Code:
Private Sub Fill_Details_to_JobRecord_Click()

    TurnOff
               
                Application.ScreenUpdating = False
                
                    
                        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
                        Dim iRow As Integer
                        
                        
                        FilePath = "\\tgs-srv01\share\ShopFloor\PRODUCTION\JOB BOOK\"
                        Filename = "JOB RECORD SHEET.xlsm"
                        
                        Set JCM = ThisWorkbook.Worksheets("Job Card Master")

                        Set SrcDataRange = JCM.Range("A13").CurrentRegion

                        
'                      On Error GoTo ErrHandler
                     With JCM
                    
                     iRow = .Range("S13:S" & .Rows.Count).Find("SELLING PRICE", LookIn:=xlValues, lookat:=xlWhole).Row
                     iRow = iRow + 4

                     End With
                     
                     Set SrcOpen = Workbooks.Open(FilePath & Filename)
                     Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
                     Windows("JOB RECORD SHEET.xlsm").Visible = True
                     
                      TGSR.Range("V").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow, 20, 0)
                      Range("V").Select
                      
                      TGSR.Range("W").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 16, 22, 0)
                      Range("W").Select
                      
                      TGSR.Range("X").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 18, 22, 0)
                      Range("X").Select
                      
                      TGSR.Range("Y").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 20, 22, 0)
                      Range("Y").Select
                      
                      TGSR.Range("Z").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 2, 22, 0)
                      Range("Z").Select
                      
                      TGSR.Range("AA").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 16, 24, 0)
                      Range("AA").Select
                      
                      TGSR.Range("AB").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 18, 24, 0)
                      Range("AB").Select
                     
                      TGSR.Range("AB").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 20, 24, 0)
                      Range("AB").Select
                    
                      SrcOpen.Close
         
'ErrHandler:
'                       If Err = 1004 Then
'                       MsgBox "Fill Job Number in Job Card Master Sheet Cell G2"
'                       End If

TurnOn
End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
I would record a macro with a vlookup formula and then use that code in my own code.
i hope that advice will help a bit
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
Please can someone help today
Sorry, I had out-of-town requests all weekend, so was off-line all weekend.
I cannot download files from my current computer, but will have access to later on today.

What I was really hoping for from you was a walk-through of a single example record, explaining what should happen for that particular example in each cell (with specific, like the cell addresses and values that are being updated).
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
Any chance of a zoom call later because it`s a lot easier for me to show you rather than me trying to explain via forums massages
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

There is an awful lot going on in that workbook, at lot of "noise" that is muddling the problem. I don't think all that is necessary.
It seems to me that the issue is that you may not be familiar with how VLOOKUP formulas work and need to be structured.

Did you look at the link on VLOOKUP that I provided last week, and at the examples provided?
What part of it is giving your trouble?

The arguments of the VLOOKUP function all work in unison.
Here are the 4 arguments (parts of the formula):

1. value
This is the value you are looking up (a single value, as you only look up one item at a time).

2. table
This is the range where you are looking for the value item.
Typically, this is a multi-column range. The first column is always the column where the "value" you are looking for resides.
Then you want to go out at least as far as the column that contains the value that you want to return when you find your match.

3. index_number
Of the columns that you have listed in the table (argument 2), this is the one that contains the value you want to return.

4. approximate_match
False - only return exact matches
True - return an approximate match, if an exact one is not found

So, let's say that you had a value in A1, and you wanted to look for the matching value in column M, and when you find the match, you want to return the value from column S, then your formula would look like:
=VLOOKUP(A1,M:S,7,FALSE)
(note that in the range of column M to column S, column S is the 7th column)
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
I understand but I need to fill found data in a different workbook that`s the bit I can`t get my head around?
Please help
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I understand but I need to fill found data in a different workbook that`s the bit I can`t get my head around?
Once you get your result, you should be able to paste it anywhere you want.
It looks to me like you aren't even getting the correct result, as your VLOOKUP function is not structured correctly.

I usually do not recommend this, but since you have an awful lot going on in your workbook that I think is muddling up the waters, it may be beneficial to make a real simplified example of what you are trying to do for this one particular task in a new workbook (that does not have any other extraneous information, data, code, etc).

So if you can create a real simple example and show the part that you are having trouble with, maybe we can work through it.
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
I tried to simplify my code by taking a lot away.
It seemed to start working as in some details transferred but now it just seems to go into a tailspin and no detail transfers??
Here is my new code

VBA Code:
Private Sub Fill_Details_to_JobRecord_Click()

    TurnOff
                        Dim SrcOpen As Workbook
                        Dim JCM As Worksheet
                        Dim TGSR As Worksheet
                        Dim FilePath As String
                        Dim Filename As String
                        Dim iRow As Integer
                        Dim i As Long
                        Dim JobNo As String


                        Set JCM = ThisWorkbook.Worksheets("Job Card Master")


                     FilePath = "\\tgs-srv01\share\ShopFloor\PRODUCTION\JOB BOOK\"
                     Filename = "JOB RECORD SHEET.xlsm"
                     
                     Set SrcOpen = Workbooks.Open(FilePath & Filename)
                     Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
                     
                     With JCM
                     iRow = .Range("S13:S" & .Rows.Count).Find("SELLING PRICE", LookIn:=xlValues, Lookat:=xlWhole).Row
                     iRow = iRow + 4
                     JobNo = JCM.Range("G2").Value
                     End With
                     
                     With TGSR
                     For i = 2 To Rows.Count
                     i = .Range("A13:A" & .Rows.Count).Find(JobNo, LookIn:=xlValues, Lookat:=xlWhole).Row
                    .Cells(i, 22) = JCM.Cells(iRow, 20)
                    .Cells(i, 23) = JCM.Cells(iRow + 11, 22)
                    .Cells(i, 24) = JCM.Cells(iRow + 13, 22)
                    .Cells(i, 25) = JCM.Cells(iRow + 15, 22)
                    .Cells(i, 26) = JCM.Cells(iRow + 2, 20)
                    .Cells(i, 27) = JCM.Cells(iRow + 11, 24)
                    .Cells(i, 28) = JCM.Cells(iRow + 13, 24)
                    .Cells(i, 29) = JCM.Cells(iRow + 15, 24)
                        Next i
                         End With
                     
                    SrcOpen.Close
TurnOn
End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,233
Office Version
  1. 2010
Platform
  1. Windows
Can you define "today" ?
Local time for me is currently 3 AM

You should test your code by stepping through it one line at a time using the F8 key.

The For-Next loop on the TGSR sheet is endless as the variable i is being calculated to the same thing within each loop
so For i = 2 To Rows.Count the i will never be anything other than the calculated value and never get out of the loop.

Also most of the cells you copy are blank.
 

Forum statistics

Threads
1,143,677
Messages
5,720,249
Members
422,272
Latest member
ginkgoVil

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