VBA Error Handling - run time error 91

JanetW

New Member
Joined
May 12, 2016
Messages
21
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this.
Code:
    Set rngfnd = ActiveSheet.Range("B:B").Find(What:="Inputs declared from Cash report", After:=ActiveCell, LookIn:=xlValues, _
                                               LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                               MatchCase:=True, SearchFormat:=False)
    If Not rngfnd Is Nothing Then
        With rngfnd.Offset(0, 3)
            .Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Cash Report'!G5", TextToDisplay:="b/f from Cash Report"
            .Font.Italic = True
        End With
    End If

    Set rngfnd = ActiveSheet.Range("B:B").Find(What:="Business related RC/AQ reclaim", After:=ActiveCell, LookIn:=xlValues, _
                                               LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                               MatchCase:=True, SearchFormat:=False)
    If Not rngfnd Is Nothing Then
        With rngfnd.Offset(0, 3)
            .Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Cash Report'!G5", TextToDisplay:="b/f from Cash Report"
            .Font.Italic = True
        End With
    End If
 
Upvote 0
Avoid "On Error" at all costs. It will hide all sorts of problems from you and cause unpredictable things to happen.

To handle this error you need to make a range variable and assign the search to it. This way if the search fails you end up with an empty variable.

Code:
Dim FoundRange as Range

FoundRange = ActiveSheet.Range("B:B")[I].Find(What:="Inputs declared from Cash report", After:=ActiveCell, LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=True, SearchFormat:=False).Activate[/I]

Once you have done that you can do normal error handling on that variable like this:

Code:
If FoundRange is nothing then 
'Your search failed
End If
 
Upvote 0
Thanks for quick response

I tried the code provided and I am now getting run time error 13 type mismatch on the set rngfnd row

I thought this might be because I hadn't declared it so added Dim rngfnd as Range but still same error .... any ideas
 
Upvote 0
Can you post the code giving you the error?
 
Upvote 0
Hi,
Rather than keep repeating the find code see if following suggestion will work for you:

Rich (BB code):
Sub SearchColumnB()
    Dim Search As Variant, SubAddressLink As Variant
    Dim ReportName As String
    Dim i As Integer
    Dim FoundCell As Range
    
    Search = Array("Inputs declared from Cash report", "Business related RC/AQ reclaim")
    SubAddressLink = Array("'Cash Report'!G5", "'Purchase Report'!I5")
    
    i = LBound(Search)
    Do
        ReportName = Mid(SubAddressLink(i), 2, InStr(1, SubAddressLink(i), " ") - 1)
        
        Set FoundCell = ActiveSheet.Columns(2).Find(What:=Search(i), After:=Cells(1, 2), LookIn:=xlValues, _
                                                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                    MatchCase:=False, SearchFormat:=False)
        
        If Not FoundCell Is Nothing Then
            With FoundCell.Offset(0, 3)
                .Hyperlinks.Add Anchor:=FoundCell.Offset(0, 3), _
                    Address:="", SubAddress:=SubAddressLink(i), _
                    ScreenTip:="Goto " & ReportName & " Report", _
                    TextToDisplay:="b/f from " & ReportName & " Report"
                .Font.Italic = True
            End With
        End If
        i = i + 1
        Set FoundCell = Nothing
    Loop Until i > UBound(Search)
    
End Sub

You will need to add to the values shown in RED as required.
Solution is untested but hopefully will give you something that you can work with.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top