Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple quick question
remove the word "set" beside the "whattofind"
you cannot "set" a value |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick help with a simple function! | Excel Discussion (Misc queries) | |||
Quick question - quick answer about assigning shortcut keys | Excel Programming | |||
Excel should have a quick and simple "change case" function like . | Excel Worksheet Functions | |||
Simple Question, need quick answer please | Excel Programming | |||
Another Quick Simple VBA question (if...then statement) | Excel Programming |