Macro Run Time Error 91

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
I was supplied this code as an answer to a post at:
http://www.ozgrid.com/forum/showthread.php?t=73583

Code:
Sub asdf() 
    Dim c As  Range 
    Dim x As Long 
    Dim y As Integer 
     
    Set c = Columns("A:B"). Find(" Total", lookat:=xlWhole) 
    x = c.Row 
    For y = 15 To 4  Step -1 
        If Cells(x, y) = 0 Then Columns(y).Delete 
    Next y 
End Sub

However, it errors out at
Code:
x = c.Row
Saying "object variable or With block variable not set"
I have xl2000. Could this be the problem?

With the code I'm trying to locate my "Total" row. Then delete all columns from D:O with a value of zero. An example sheet is supplied at the link.
Thank you very much for your time and help.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
The error is more than likely because of the Find not finding anything.

PS Could you not have continued in the original thread in the other forum?

LeonS posted there only a minute ago.:)
 
Upvote 0
Try this out.

Code:
Sub asdf()
Dim C as Range, x as Long, y as Long

Set C = Columns("A:B").Find("Total", lookat:=xlWhole)
If Not C is Nothing then 
x = C.Row
For y = 15 To 4 Step -1
    If Cells(x, y) = 0 Then Columns(y).Delete
Next y

End Sub

Hope this helps!
 
Upvote 0
PS Could you not have continued in the original thread in the other forum?

LeonS posted there only a minute ago.

Yeah, I think we posted at the same time that's why I didn't see his untill right after. :cool:
PS the find works, that's the part I supplied to the process. I just don't know where to go from there.
 
Upvote 0
erock24

If the find works you shouldn't be having any problems.

What Brian's code does is take care of instances when the find doesn't work.
 
Upvote 0
Erock,


Basically what was causing the error was trying to assign x a value when the range C did not exist. My code simply skips over the assignment and deletion of the row if "Total" was not found. BTW I forgot an End if in my code, I am supposing if you got it working you figured that out.

Code:
Sub asdf() 
Dim C as Range, x as Long, y as Long 

Set C = Columns("A:B").Find("Total", lookat:=xlWhole) 
If Not C is Nothing then 
    x = C.Row 
    For y = 15 To 4 Step -1 
        If Cells(x, y) = 0 Then Columns(y).Delete 
    Next y 
End if

End Sub
 
Upvote 0
Are you sure all you added to your code was End If?
 
Upvote 0
How can I add 1 more condition to this macro.
I only need the columns deleted if they equal zero in the total row and if the date in row 7 is greater than today.(or the month is greater than current month).

Thank you for your time and help.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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