Trying to return a row number with find method

[Darren Smith]

I am trying to return the row number but it says Run-Time error 91 on the line below...
Any ideas why???

iRow = .Range("S13:S" & .UsedRange.Rows.Count).Find("GROSS PROFIT ANALYSIS", LookIn:=xlValues, lookat:=xlWhole).Row

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" & .UsedRange.Rows.Count).Find("GROSS PROFIT ANALYSIS", LookIn:=xlValues, lookat:=xlWhole).Row
                     iRow = iRow + 7

                     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, 22, 0)
                      Range("V").Select
                   
                  
                      SrcOpen.Close
       
'ErrHandler:
'                       If Err = 1004 Then
'                       MsgBox "Fill Job Number in Job Card Master Sheet Cell G2"
'                       End If

TurnOn
End Sub

 

[Joe4]

what happens when you unmerge the cells and test it ?

I missed that you mentioned it is in a merged cell.
Merged cells are notorious sources of pain (especially with VBA!), and should really be avoided at ALL costs!
What cells is S159 merged with?
What happens when you unmerge the cell and test the code?

 

[Darren Smith]

Sorry I`ve got it to work now by taking used range away. Also, I was using an auto recovered workbook which was messing things up.

 

[Joe4]

Glad you got it working out.
However, if possible, I would still recommend trying to get rid of the merged cells.
If merging across single rows, see this alternative: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[Darren Smith]

Please could you help with my lastest question?