Macro Run Time Error 91

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,161
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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!
 

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,161
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Are you sure all you added to your code was End If?
 

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,161
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.
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

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