View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
 
Posts: 9,101
Default Macro instead of lookup

Using a worksheet function where you can use a VBA function is inefficient.

RowCount = 2
Person = Range("A" & RowCount)
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
Set LookupRange = Range(Range("B" & RowCount), Cells(2, LastCol))
Set c = Rows(RowCount).Find(what:=Person, _
LookIn:=xlValues, lookat:=xlWhole)
MsgBox (Person & " found at " & c.Address)


Try this

set c =

"Jacob Skaria" wrote:

Try this

Application.HLookup(Range("A2"),Range("B2:F6"),2)

OR

Application.Index(Range("B2:F6"),2,Application.Mat ch(Range("A2"),Range("B2:F2"),0))

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


"Kashyap" wrote:

ColA ColB ColC ColD ColE ColF
Calvin Amy Andy Calvin Danie Greg
ABC GEF LMO GEF LMO
GEF LMO IJK LMO
LMO IJK IJK
IJK


In the above table, desiredoutput in ColA below Calvin

LMO
IJK

formula used
=HLOOKUP(A2,B2:F6,2)
=HLOOKUP(A2,B2:F6,3)