Hard to explain - Excel Macro Code

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