Error 91
 

I'm using this code to search for a certain 5 digit number eg 88860

Dim cl As Long
Sheets("Milestone").Select
Range("C2").Select
cl = ActiveCell 'Set cl as number 88860
Sheets("Master").Select
Range("A4").Select
Cells.find(What:=cl, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

and an error comes up at this point saying something like I have not set the
variable. I've used this code loads iof times before with no problem, so I
don't know whats up. Any help?


--
Dave

Error 91
 

Hi
Try
cl = Activecell.Value

regards
Paul

as cl is a range object.
DaveyJones wrote:
I'm using this code to search for a certain 5 digit number eg 88860

Dim cl As Long
Sheets("Milestone").Select
Range("C2").Select
cl = ActiveCell 'Set cl as number 88860
Sheets("Master").Select
Range("A4").Select
Cells.find(What:=cl, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

and an error comes up at this point saying something like I have not set the
variable. I've used this code loads iof times before with no problem, so I
don't know whats up. Any help?


--
Dave

Error 91
 

I gave it a go but the error still comes up. The rest of the code works great
if I just set a cell as the activecell then run the sub, but as soon as I try
to set the activecell with the find function, it gives me the error.
--
Dave


" wrote:

Hi
Try
cl = Activecell.Value

regards
Paul

as cl is a range object.
DaveyJones wrote:
I'm using this code to search for a certain 5 digit number eg 88860

Dim cl As Long
Sheets("Milestone").Select
Range("C2").Select
cl = ActiveCell 'Set cl as number 88860
Sheets("Master").Select
Range("A4").Select
Cells.find(What:=cl, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

and an error comes up at this point saying something like I have not set the
variable. I've used this code loads iof times before with no problem, so I
don't know whats up. Any help?


--
Dave

Error 91
 

The number was not found, so there is no cell to activate.
This works...

Sub AreYouThere()
Dim cl As Double
Dim rngFound As Excel.Range

cl = Sheets("Milestone").Range("C2").Value 'Set cl as number 88860
Set rngFound = Sheets("Master").Cells.Find(What:=cl, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rngFound Is Nothing Then
Application.Goto rngFound, True
MsgBox rngFound.Address(external:=True)
Else
MsgBox cl & " not found. "
End If
End Sub
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DaveyJones"

wrote in message
I'm using this code to search for a certain 5 digit number eg 88860

Dim cl As Long
Sheets("Milestone").Select
Range("C2").Select
cl = ActiveCell 'Set cl as number 88860
Sheets("Master").Select
Range("A4").Select
Cells.find(What:=cl, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= False).Activate

and an error comes up at this point saying something like I have not set the
variable. I've used this code loads iof times before with no problem, so I
don't know whats up. Any help?
--
Dave

Error 91
 

Oh how I love the excel error messages. Thanks alot Jim. If i'd have known
that I could have sorted it myself. I missed off a possible value of cl so it
was not finding it, thanks alot.
--
Dave


"Jim Cone" wrote:

The number was not found, so there is no cell to activate.
This works...

Sub AreYouThere()
Dim cl As Double
Dim rngFound As Excel.Range

cl = Sheets("Milestone").Range("C2").Value 'Set cl as number 88860
Set rngFound = Sheets("Master").Cells.Find(What:=cl, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rngFound Is Nothing Then
Application.Goto rngFound, True
MsgBox rngFound.Address(external:=True)
Else
MsgBox cl & " not found. "
End If
End Sub
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DaveyJones"

wrote in message
I'm using this code to search for a certain 5 digit number eg 88860

Dim cl As Long
Sheets("Milestone").Select
Range("C2").Select
cl = ActiveCell 'Set cl as number 88860
Sheets("Master").Select
Range("A4").Select
Cells.find(What:=cl, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= False).Activate

and an error comes up at this point saying something like I have not set the
variable. I've used this code loads iof times before with no problem, so I
don't know whats up. Any help?
--
Dave