Excel Forum

Code: Select all

'Other code
With WorksheetFunction
'   intCount = .CountIf(sh.Range("B2:B" & lngLast), strTextFinal)
    intCount = FoundOnLineNo(sh.Range("b2:b" & inglast), strTextFinal)
    If intCount > 0 Then
'       strResult = MatchCAi(strTextFinal)
        strResult = sh.Range("c" & intCount).Value
        'Para.Range.Text = strResult
        Para.Range.Text = Replace(Para.Range.Text, Para.Range.Text, strResult & Chr(13))
    Else
        Para.Range.Font.ColorIndex = wdRed
        Para.Range.Text = "Name not match in CAI"
    End If
End With
'Other code

Function FoundOnLineNo(dbAll As Range, strFind As String) As Long
    Dim r As Range
        If InStr(strFind, r.Value) Then
            FoundOnLineNo = r.Row
            Exit Function
        End If
    Next r
End Function

Code: Select all

Function FoundOnLineNo(dbAll As Range, strFind As String) As Long
    Dim r As Range
    
    For Each r In dbAll
        If InStr(strFind, r.Value) Then
            FoundOnLineNo = r.Row
            Exit Function
        End If
    Next r
    
End Function