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 |
simple quick question
remove the word "set" beside the "whattofind"
you cannot "set" a value |
simple quick question
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" wrote in message ... 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 |
All times are GMT +1. The time now is 08:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com