Macro instead of lookup
 

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

Macro instead of lookup
 

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

Macro instead of lookup
 

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

Macro instead of lookup
 

can I have the full code pls

Macro instead of lookup
 

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

Macro instead of lookup
 

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

Macro instead of lookup
 

I'm getting type mismatch error

Macro instead of lookup
 

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

Macro instead of lookup
 

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)

Macro instead of lookup
 

you have a look at the file at the below link

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

Macro instead of lookup
 

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)

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)

Macro instead of lookup
 

Compilation error

Expected:=

Macro instead of lookup
 

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

"Kashyap" wrote:

Compilation error

Expected:=

Macro instead of lookup
 

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:=

Macro instead of lookup
 

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:=

Macro instead of lookup
 

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:=

Macro instead of lookup
 

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:=

Macro instead of lookup
 

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