I need to fill details to another workbook

[Darren Smith]

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?

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

 

[Darren Smith]

Today for me is 11:30 in the morning

I`ve taken the loop away and redone the code but it says that iRow is an invalid qualifier???
The bottom of the code is different from before.

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 SHEET1.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

                     End With
                     
                     JobNo = JCM.Range("G2").Value
               
                     With TGSR
                     
                     i = .Range("A13:A" & .Rows.Count).Find(JobNo, LookIn:=xlValues, lookat:=xlWhole).Row
                    .Cells(i, 22) = JCM.Cells(iRow.Offset(0, 20)).Value
                    .Cells(i, 23) = JCM.Cells(iRow.Offset(11, 22)).Value
                    .Cells(i, 24) = JCM.Cells(iRow.Offset(13, 22)).Value
                    .Cells(i, 25) = JCM.Cells(iRow.Offset(15, 22)).Value
                    .Cells(i, 26) = JCM.Cells(iRow.Offset(2, 20)).Value
                    .Cells(i, 27) = JCM.Cells(iRow.Offset(11, 24)).Value
                    .Cells(i, 28) = JCM.Cells(iRow.Offset(13, 24)).Value
                    .Cells(i, 29) = JCM.Cells(iRow.Offset(15, 24)).Value
              
                         End With
                                      
                                      
                        SrcOpen.Close
TurnOn
End Sub

 

[NoSparks]

iRow is a number, you can't offset from a number, you can from a cell but not a number
just change the +numbers you had to be using the right rows.

 

[Darren Smith]

iRow is a number, you can't offset from a number, you can from a cell but not a number
just change the +numbers you had to be using the right rows.

That way I had it did not work so where do i go from here?

 

[Joe4]

Please could somebody help today

Believe it or not, most of us do have lives outside of this and are not online 24 hours a day!
Everyone here volunteers of their time freely, none of us get paid to do this.
And many of us are in different time zones than you (it is 7:50 AM here for me).

That way I had it did not work so where do i go from here?

Did you try stepping through the code line-by-line, using the F8 key, like No Sparks suggested?
If you do this, you can often see what is going on. While you are doing that, if you hover over any variable, it will show you its variable at that point in time.

When trying to debug code that does not work, I will often also add MsgBox's at various points in my code to return the value of certain variables, so I can confirm the value of them.

Also, when I am dealing with a difficult or involved task, I usually start out with the smallest part. When I get that working, then I add the next part. And I continue on in that fashion until I run into issues, or get it working. That way, you can identify exactly where the issue is occurring.

 

[Darren Smith]

Don`t get angry with me I am just asking for help. I know your volunteers I am sorry to go on??
I have been through the code using F8 and it all works except the last part can`t seem to add rows to the found row iRow. If I could have help with that it would work I think. Where it says iRow + 11 it does not find that row?

 .Cells(i, 22) = JCM.Range("T1", JCM.Cells(iRow, 20)).Value
                    .Cells(i, 23) = JCM.Range("T1", JCM.Cells(iRow + 11, 22)).Value

 

[Joe4]

Don`t get angry with me I am just asking for help. I know your volunteers I am sorry to go on??

I am not angry at all. I just want to make sure you have realistic expectations.
To increase your chances of getting what you need, it is best not appear to be impatient or unappreciative. I often see helpers walk away if they start sensing that.

I have been through the code using F8 and it all works except the last part can`t seem to add rows to the found row iRow. If I could have help with that it would work I think. Where it says iRow + 11 it does not find that row?

I am a bit confused as to what you are trying to do with that line.
It looks like you are are trying to put a whole range of values into a single cell.
Can you explain what this line is supposed to do, exactly?

 

[NoSparks]

Don't know where that code came from.

Yes the row iRow + 11 is found, it's just that the cell you want to copy isn't in that row.
Maybe you should just be adding 10 instead of 11 ?

                     With TGSR
                     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 + 10, 22)

 

[Darren Smith]

The iRow is the correct row the idea is to find that row then drop the code down and or across to read the cells below then select that value.
After that, the value should end up in the other excel file spreadsheet.

 

[Darren Smith]

Sorry me getting over complex what you have done is perfect thank you very much.
I shall stop bugging people now???

 

[NoSparks]

Interesting, good luck in the future.