Is this possible within Excel 2000?

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

Dave Peterson

To actually move the selection, you'd need to use a little program.

But if you meant, you wanted to return the value from (say) the third column
over in the table when a match is found, you could use a worksheet formula.
Look at =vlookup() in help:

=vlookup(a1,sheet2!$a$1:$z$99,3,false)

will match up what's in A1 of the current sheet with Sheet2's A1:A99 and bring
back the value in the third column (sheet2's column C).

Option Explicit
Sub testme()

Dim myCell As Range
Dim myLookinCol As Range
Dim res As Variant

With Worksheets("sheet2")
Set myLookinCol = .Range("a4:a28")
Set myCell = ActiveCell 'or whatever you want

res = Application.Match(myCell.Value, myLookinCol, 0)
If IsError(res) Then
MsgBox "not found in table"
Else
'2 columns to the right (still column C)
Application.Goto myLookinCol(res).Offset(0, 2)
End If
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Hi, I've used Excel on a get by basis for many years at work, and I'm just
beginning to realise its vast capabilities, having viewed several groups
which I didn't know existed previously.
I subsequently would like to ask if it is possible for Excel to look at the
numerical or text content of a cell on one worksheet and then look at a
predefined range on another worksheet and find the equivalent cell content
and then move the cursor to that cell? This has me stumped presently.
Thanks in advance for any assistance.
J.J.
 
Back
Top