View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Jacob Skaria Jacob Skaria is offline
external usenet poster
 
Posts: 8,520
Default Look up text in an array and select (activate) the cell in whi

So you can replace the fixed string to the cell as below

Replace
<varSearch = "jacob"
with

varSearch = Range("A1")

If this post helps click Yes
---------------
Jacob Skaria


"Wes_A" wrote:

The problem is that the part number is volatile so I need the Find function
to pick up the volatile content of the cell.
Appologies for the missunderstanding.

"Jacob Skaria" wrote:

Try the below macro...

Sub Macro()

Dim varRange As Range
Dim varFound As Variant, varSearch As Variant
varSearch = "jacob"
Set varRange = ActiveSheet.Range("A1:J20")
'Replace xlPart with xlWhole if you need a whole cell match
Set varFound = varRange.Find(varSearch, lookat:=xlPart)
If Not varFound Is Nothing Then varFound.Activate

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Wes_A" wrote:

Hi all, Using Excel 2007 Macro I need to look up a part number (variable) in
an array containing all part numbers and select & activate the cell in the
array where it is found.
Can anyone help?