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

 

[Norie]

Most likely that the Find hasn't actually found what you are looking for.

 

[Joe4]

Yes. You will get that message if it cannot find that value in that range.
So you may need to use some error handling on that error to in order to handle that situation.

 

[Darren Smith]

That value is in the range I`ve made sure of it? The Value is in merged cells is that not going to work?

 

[Joe4]

That value is in the range I`ve made sure of it?

What cell address is it in?
Does it match EXACTLY?
Where is this data originally coming from (the web, external report, etc)?
The reason I ask is because I often see data that comes from the Web or other programs use special spaces instead of ordinary spaces, which causes things not to match,

 

[Darren Smith]

What cell address is it in? s159, This will vary so thats why i am using the find method
Does it match EXACTLY? Yes i copied it straight from the spreadsheet
Where is this data originally coming from (the web, external report, etc)? No just of a excel spread sheet
The reason I ask is because I often see data that comes from the Web or other programs use special spaces instead of ordinary spaces, which causes things not to match, I see

 

[Norie]

What does this return.

Res = Application.Match("GROSS PROFIT ANALYSIS", JCM.Range("S:S"), 0)

If IsError(Res) Then
    MsgBox "GROSS PROFIT ANALYSIS not found"
Else
    iRow = Res
End if

 

[Joe4]

Try removing the .UsedRange from the iRow calculation and see if it works, i.e. change this row:

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

to this:

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

If that still does not work, run this code and tell me what it returns:

Sub Test1()

    Dim JCM As Worksheet
    Dim iRow As Long
    
    Set JCM = ThisWorkbook.Worksheets("Job Card Master")
    
    MsgBox JCM.Range("S159") = "GROSS PROFIT ANALYSIS"
    
End Sub

 

[NoSparks]

The Value is in merged cells is that not going to work?

what happens when you unmerge the cells and test it ?

 

[Darren Smith]

Try removing the .UsedRange from the iRow calculation and see if it works, i.e. change this row:

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

to this:

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

If that still does not work, run this code and tell me what it returns:

Sub Test1()

    Dim JCM As Worksheet
    Dim iRow As Long
   
    Set JCM = ThisWorkbook.Worksheets("Job Card Master")
   
    MsgBox JCM.Range("S159") = "GROSS PROFIT ANALYSIS"
   
End Sub

I tried taking the used range away but no luck.
The Msgbox says True