simple quick question

[Guest]

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

 

[tiah]

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

 

[Bob Phillips]

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)