How to make a macro which works same as hlookup?

Eg: I have some data in a different file and I need to get those to active
sheet. if value in range a1:z1 matches a1.value in active sheet then it
should get value from corresponing cloumn

You can try

Worksheetfunction.HLookup

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


"Kashyap" wrote:

How to make a macro which works same as hlookup?

Eg: I have some data in a different file and I need to get those to active
sheet. if value in range a1:z1 matches a1.value in active sheet then it
should get value from corresponing cloumn

Adapt to suit

Sub hlookupa()
MsgBox Application.hlookup(Range("b5"), Range("a1:z1"), 1, 0)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kashyap" wrote in message
...
How to make a macro which works same as hlookup?

Eg: I have some data in a different file and I need to get those to active
sheet. if value in range a1:z1 matches a1.value in active sheet then it
should get value from corresponing cloumn

can I have the full code pls

set c = Rows(1).find(what:="abc",lookin:=xlvalues,lookat:= xlwhole)

then test fo c
if c is nothing then
msgbox("count not find abc")
else
msgbox("data found in column " & c.column)
end if

"Kashyap" wrote:

How to make a macro which works same as hlookup?

Eg: I have some data in a different file and I need to get those to active
sheet. if value in range a1:z1 matches a1.value in active sheet then it
should get value from corresponing cloumn

WorksheetFunction.hlookup("abc",Range("a1:z1"), 1, 0)

OR try with INDEX and MATCH

Msgbox Application.Index(Range("A1:Z10"),1, _
Application.Match("abc", Range("A1:Z1"), 0))

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


"Kashyap" wrote:

can I have the full code pls

I'm getting type mismatch error

The range is having numbers or text?

Just post back with how the data is arranged and your current code..

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


"Kashyap" wrote:

I'm getting type mismatch error

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)

you have a look at the file at the below link

http://www.savefile.com/files/2116453

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)

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)

Compilation error

Expected:=

Do you have two postings mixed up? Expected isn't showing up anywhere in tis
posting.

"Kashyap" wrote:

Compilation error

Expected:=

As Joel mentioned it may be inefficient. Since your original query was around
hookup try this one..from VBEimmediate window

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


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


"Kashyap" wrote:

Compilation error

Expected:=

Application is still performing a worksheet function. You code is no
different with or without the APPLICATION.

"Jacob Skaria" wrote:

As Joel mentioned it may be inefficient. Since your original query was around
hookup try this one..from VBEimmediate window

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


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


"Kashyap" wrote:

Compilation error

Expected:=

Joel, I understand it is a worksheet function and I have not mentioned in my
previous post that it is an *** alternative *** VBA function .

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


"Joel" wrote:

Application is still performing a worksheet function. You code is no
different with or without the APPLICATION.

"Jacob Skaria" wrote:

As Joel mentioned it may be inefficient. Since your original query was around
hookup try this one..from VBEimmediate window

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


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


"Kashyap" wrote:

Compilation error

Expected:=

Hi Jacob,

I'm now getting

compilation error
Method not valid without suitable object



"Jacob Skaria" wrote:

As Joel mentioned it may be inefficient. Since your original query was around
hookup try this one..from VBEimmediate window

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


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


"Kashyap" wrote:

Compilation error

Expected:=

using below code, it says data found in 2 columns, but not able to paste data
to a different row



"Joel" wrote:

set c = Rows(1).find(what:="abc",lookin:=xlvalues,lookat:= xlwhole)

then test fo c
if c is nothing then
msgbox("count not find abc")
else
msgbox("data found in column " & c.column)
end if

"Kashyap" wrote:

How to make a macro which works same as hlookup?

Eg: I have some data in a different file and I need to get those to active
sheet. if value in range a1:z1 matches a1.value in active sheet then it
should get value from corresponing cloumn