Hard to explain - Excel Macro Code

  • Thread starter Thread starter Amanda Knott
  • Start date Start date
A

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
 
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
 
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
 
You should post your troublesome code, but I'm betting you used:

foundcell = cells.find(....

instead of:

Set foundcell = cells.find(....
 
Back
Top