VBA Error Handling - run time error 91

[JanetW]

Hi

I have pasted an extract from some code I am writing below. I have included on error goto XXX to skip this block if the criteria cannot find the data but it is still failing. I appreciate that it may not find the text being searched for - hence the reason for the goto - but it keeps sticking on the italic area. I assume this is because it can't activate the cell as it can't find the search criteria but I thought the on error goto would bypass this???


Can anybody help please????

H15:

ActiveSheet.Range("B:B").Select
On Error GoTo H16

Selection.Find(What:="Inputs declared from Cash report", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Cash Report'!G5", TextToDisplay:="b/f from Cash Report"
Selection.Font.Italic = True
GoTo H16

H16:

ActiveSheet.Range("B:B").Select
On Error GoTo H17
Selection.Find(What:="Business related RC/AQ reclaim", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Report'!I5", TextToDisplay:="b/f from Purchase Report"
Selection.Font.Italic = True
GoTo H17

 

[JanetW]

Janet,
I neglected to make one minor change to the common code & you may get an error.

Use this version:

Sub SearchColumn(ByVal sh As Object, ByVal Search As Variant, ByVal SearchColumn As Variant, ByVal CellOffset As Integer, ByVal SubAddressLink As Variant, ByVal TextDisplay As String)
    Dim ReportName As String
    Dim i As Integer
    Dim FoundCell As Range
    
    i = LBound(Search)
    Do
        ReportName = Mid(SubAddressLink(i), 2, InStr(2, SubAddressLink(i), "'") - 2)
        
        Set FoundCell = sh.Columns(SearchColumn).Find(What:=Search(i), After:=sh.Cells(1, SearchColumn), LookIn:=xlValues, _
                                                            LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                                            SearchDirection:=xlNext, MatchCase:=False, _
                                                            SearchFormat:=False)
        
        If Not FoundCell Is Nothing Then
            With FoundCell.Offset(0, CellOffset)
                .Hyperlinks.Add Anchor:=FoundCell.Offset(0, 3), _
                    Address:="", SubAddress:=SubAddressLink(i), _
                    ScreenTip:="Goto " & ReportName, _
                    TextToDisplay:=TextDisplay & ReportName
                .Font.Italic = True
            End With
        End If
        i = i + 1
        Set FoundCell = Nothing
    Loop Until i > UBound(Search)
    
End Sub

Dave

Thanks Again!!