Hard to explain - Excel Macro Code

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

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 wrote:
>
> 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

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
On Tue, 02 May 2006 17:53:53 -0500, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>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 wrote:
>>
>> 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

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

foundcell = cells.find(....

instead of:

Set foundcell = cells.find(....

Amanda Knott wrote:
>
> 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
> On Tue, 02 May 2006 17:53:53 -0500, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
>
> >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 wrote:
> >>
> >> 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