simple quick question

I want to find the active cell in a list in another worksheet... this gets
and error on the WhatToFind line... any ideas?

ActiveCell.Copy
Range("z1").Select
ActiveCell.PasteSpecial xlPasteValues
Range("a1").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim WhatToFind As Variant

Application.ScreenUpdating = False
Sheets("Contacts").Select
Set wks = Sheets("Assignments")
Set rngToSearch = Sheets("contacts").Columns(1)
Set WhatToFind = wks.Range("z1").Value

Set rngFound = rngToSearch.find(what:=WhatToFind, LookIn:=xlValues,
lookat:=xlWhole)

If rngFound Is Nothing Then
MsgBox "error"
Else
rngFound.Select
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

remove the word "set" beside the "whattofind"
you cannot "set" a value

Not tested, but you set a range not a value

ActiveCell.Copy
Range("z1").Select
ActiveCell.PasteSpecial xlPasteValues
Range("a1").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim WhatToFind As Variant

Application.ScreenUpdating = False
Sheets("Contacts").Select
Set wks = Sheets("Assignments")
Set rngToSearch = Sheets("contacts").Columns(1)
WhatToFind = wks.Range("z1").Value

Set rngFound = rngToSearch.find(what:=WhatToFind, LookIn:=xlValues,
lookat:=xlWhole)

If rngFound Is Nothing Then
MsgBox "error"
Else
rngFound.Select
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" <[email protected]> wrote in message
news:[email protected]...
> I want to find the active cell in a list in another worksheet... this gets
> and error on the WhatToFind line... any ideas?
>
> ActiveCell.Copy
> Range("z1").Select
> ActiveCell.PasteSpecial xlPasteValues
> Range("a1").Select
> Dim rngToSearch As Range
> Dim wks As Worksheet
> Dim rngFound As Range
> Dim WhatToFind As Variant
>
> Application.ScreenUpdating = False
> Sheets("Contacts").Select
> Set wks = Sheets("Assignments")
> Set rngToSearch = Sheets("contacts").Columns(1)
> Set WhatToFind = wks.Range("z1").Value
>
> Set rngFound = rngToSearch.find(what:=WhatToFind, LookIn:=xlValues,
> lookat:=xlWhole)
>
> If rngFound Is Nothing Then
> MsgBox "error"
> Else
> rngFound.Select
> End If
> Application.ScreenUpdating = True
> Application.Calculation = xlCalculationAutomatic
>
> End Sub