vba issue with error handling

Question

All the informations about the my sheets are explained HERE. But I'll explain it quickly:

I have 3 sheets (Plan1, BANCO and DB). Plan1 has named ranges where I insert information and this information is stored on BANCO and copied to BD (this last one saves all past informations while BANCO only has last information inserted).

I also have a code to verify if the named range alocacao already exists on BD and if it exists, they're load again on Plan1. After this, you can change the name of alocacao after inserting the new name in a range named substituit_aloc using the code below:

Sub SubstituirProduto_Click()
Dim FoundCell As Range, FirstAddr As String, fnd As String, newAloc As Range, i As Long

On Error GoTo Catch

    fnd = Sheets("Plan1").Range("alocacao").Value
    Set newAloc = Sheets("Plan1").Range("substituir_aloc")

    Set FoundCell = Sheets("BD").Columns(5).Find(what:=fnd, _
        After:=Sheets("BD").Cells(Rows.Count, 5), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If

    Do Until FoundCell Is Nothing
        i = i + 1
        FoundCell.Value = newAloc.Value

        Set FoundCell = Sheets("BD").Columns(5).FindNext(After:=FoundCell)
        If FoundCell.Address = FirstAddr Or i >= 30 Then
            Exit Do
        End If
    Loop

Catch:
MsgBox "Substituido!"

End Sub

Sometimes it works and sometimes it gets:

Run-time error 91: Object variable or With block variable not set

And highlithed line:

If FoundCell.Address = FirstAddr Or i >= 30 Then

Despite the error, it does what it need to without influencing the end result. Thus I added the On Error GoTo Catch just to doesn't show the error message and finishs to run the code, but I'm still getting the error message.

Someone know why it is still showing the error message without being catched by my error handling?

Add Option Explicit to the very top of your module and then compile. — braX
– braX, Commented Jul 28, 2017 at 14:23
@braX I already did that and get the same thing. The problem is that sometimes it works fine but sometimes it still get the error without a reason (at least for me). — paulinhax
– paulinhax, Commented Jul 28, 2017 at 14:24
@braX without the error handling the error occurs on line If FoundCell.Address = FirstAddr Or i >= 30 Then — paulinhax
– paulinhax, Commented Jul 28, 2017 at 14:26

braX · Accepted Answer · 2017-07-28 14:30:12Z

1

If Not FoundCell Is Nothing Then
  If FoundCell.Address = FirstAddr Or i >= 30 Then
     Exit Do
  End If
End If

answered Jul 28, 2017 at 14:30

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

paulinhax Over a year ago

why If Not FoundCell Is Nothing Then instead of If FoundCell Is Nothing Then?

braX Over a year ago

Because it if is Nothing it will not have an Address property to read.

Slaqr · Accepted Answer · 2017-07-28 14:30:51Z

0

In the code below the FoundCell might be set to nothing (if the findnext returns nothing) before the address is tested, this'll give an error.

    Set FoundCell = Sheets("BD").Columns(5).FindNext(After:=FoundCell)
    If FoundCell.Address = FirstAddr Or i >= 30 Then
        Exit Do
    End If

A test to see if FoundCell is nothing before the address-test might solve this:

Do Until FoundCell Is Nothing
    i = i + 1
    FoundCell.Value = newAloc.Value

    Set FoundCell = Sheets("BD").Columns(5).FindNext(After:=FoundCell)
    If FoundCell is nothing then Exit Do
    If FoundCell.Address = FirstAddr Or i >= 30 Then Exit Do
Loop

answered Jul 28, 2017 at 14:30

Slaqr

5634 silver badges12 bronze badges

Collectives™ on Stack Overflow

vba issue with error handling

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related