Hard to explain - Excel Macro Code

[Amanda Knott]

Okay - this is really tough for me to explain but I have a code for my
macro that I have an error on and I CANNOT figure out why! I am not
too good at explaining this so please ask any questions on info you
may need!!! Thank you so much in advance!!! Here is my code - please
please help me!

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/2/2006 by Authorized User
'
' Keyboard Shortcut: Ctrl+z
'
Sheets("Corp").Visible = True
ActiveSheet.Select
Range("A2:M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Range("A2").Select
Cells.Find(What:="unused", After:=ActiveCell, LookIn:=xlValues,
LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("A2:M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Range("A2").Select
End Sub

I am getting a run-time error '91' Object Variable or With Block
Variable not set and it is highlighting where it says
Cells.Find(What:="unused", After:=ActiveCell, LookIn:=xlValues,
LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
SearchFormat:=False).Activate

Thanks in advance for your help on this!!!
Amanda

 

[Dave Peterson]

If "unused" isn't found in that range, then you'll get that error.

The common fix for this is to do something like:

dim FoundCell as Range
'....
set foundcell = Cells.Find(What:="unused", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, SearchFormat:=False)

if foundcell is nothing then
'not found code goes here
else
'found code goes here
end if

 

[Amanda Knott]

Thank you for your response - Sorry it took me so long to get back to
you. This kinda had to be pushed aside for a few other things. I am
back working on this and it is erroring out again. I took your advice
and declared FoundCell as a range then I set FoundCell = Cells.Find...
and I am getting a "Run-Time Error '424' Object Required" error. Do
you think you can help me out any further?
TIA
Amanda

 

[Dave Peterson]

You should post your troublesome code, but I'm betting you used:

foundcell = cells.find(....

instead of:

Set foundcell = cells.find(....