Macro instead of lookup

[Kashyap]

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

 

[Jacob Skaria]

You can try

Worksheetfunction.HLookup

If this post helps click Yes

 

[Don Guillett]

Adapt to suit

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

 

[Kashyap]

can I have the full code pls

 

[Joel]

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

 

[Jacob Skaria]

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

 

[Kashyap]

I'm getting type mismatch error

 

[Jacob Skaria]

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

 

[Kashyap]

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)

 

[Kashyap]

you have a look at the file at the below link

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

 

[Jacob Skaria]

Try this

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

OR

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

If this post helps click Yes

 

[Joel]

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 =

 

[Kashyap]

Compilation error

Expected:=

 

[Joel]

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

 

[Jacob Skaria]

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

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


If this post helps click Yes

 

[Joel]

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

 

[Jacob Skaria]

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

 

[Kashyap]

Hi Jacob,

I'm now getting

compilation error
Method not valid without suitable object

 

[Kashyap]

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