Do Loop While error 91 "Object variable or With block variable not set"

[tico_ocit]

Hi there, need help on this..

tipo_despesa="Bank Comission"

ActiveSheet.Range("E6:E" & lastcell_geral).Select

Set despesa_encontrada = Selection.Find(What:=tipo_despesa, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False)

If Not despesa_encontrada Is Nothing Then
firstadress = despesa_encontrada.Address
despesa_encontrada.Value = ""
despesa_encontrada.Interior.ColorIndex = 35
    Do
        despesa_encontrada.Interior.ColorIndex = 35
        despesa_encontrada.Value = ""
        Set despesa_encontrada = Selection.FindNext([B]despesa_encontrada[/B])
     [I]Loop While Not despesa_encontrada Is Nothing And despesa_encontrada.Address <> firstadress[/I]

End If

Everything works great, until despesa_encontrada is Nothing. On the italic part gives me error 91 : "Object variable or With block variable not set"

I think I have all right.
Thank you in advance.

 

[Fluff]

How about

tipo_despesa = "Bank Comission"

ActiveSheet.Range("E6:E" & lastcell_geral).Select

Set despesa_encontrada = Selection.Find(What:=tipo_despesa, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False)

If Not despesa_encontrada Is Nothing Then
despesa_encontrada.Value = ""
despesa_encontrada.Interior.ColorIndex = 35
    Do
        despesa_encontrada.Interior.ColorIndex = 35
        despesa_encontrada.Value = ""
        Set despesa_encontrada = Selection.FindNext(despesa_encontrada)

     Loop While Not despesa_encontrada Is Nothing

End If

 

[tico_ocit]

How about

Work perfectly! Thank you! It was so easy, but sometimes we find ourselfs spinning around on a minor problem. Luckly it's solved!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[tico_ocit]

You're welcome & thanks for the feedback.

What do you think it's the best method when the lastcell_geral will be like 3000?? That one or this?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim varray as variant
Dim a as long
varray = ActiveSheet.Range("E6:E" & lastcell_geral).Value
For a = 1 To UBound(varray, 1)
       
        If varray(a, 1) = tipo_despesa Then
            ActiveSheet.Range("E" & a + 5).Value = ""
            ActiveSheet.Range("E" & a + 5).Interior.ColorIndex = 35
        Else
        End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Thank you in advance!

[EDIT] I had "Application.Calculation", because I realize it takes 5 seconds for 224 rows on both methods.

 

[Fluff]

If it takes 5 seconds for 224 rows, then you have something else going on. Both codes should be pretty much instant on a small amount of data.
That said, the new code should be a lot faster on large amounts of data.

 

[tico_ocit]

If it takes 5 seconds for 224 rows, then you have something else going on. Both codes should be pretty much instant on a small amount of data.
That said, the new code should be a lot faster on large amounts of data.

Ok, less a bit, 2 seconds
Didn't know this method, is very powerfull! And a lot easier to "design".
Thank you, once again!

 

[Fluff]

Glad to help & thanks for the feedback.