Look up text in an array and select (activate) the cell in which f
 

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?

Look up text in an array and select (activate) the cell in which f
 

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?

Look up text in an array and select (activate) the cell in whi
 

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?

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?