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

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
Hi there, need help on this..
VBA Code:
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,769
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,769
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95

ADVERTISEMENT

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?
VBA Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,769
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 5, 2019
Messages
95
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,769
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,977
Messages
5,639,350
Members
417,082
Latest member
rbaltero

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
Top