Vlook up macro

[lucki_87]

I know how to use vlookup as a function

but i need to create a macro

1) create a button that when click would perform the macro

2) the macro will perform the vlookup function

i need help!

 

[Joe4]

Try using the Macro Recorder as you create a VLOOKUP formula. That should give the code you need to use VLOOKUP in VBA.

 

[lucki_87]

i did that but it gave me such wierd code

i need to use range with this code too

 

[Joe4]

i did that but it gave me such wierd code

i need to use range with this code too

Don't worry about the weird code, that can be cleaned up a bit.
What do you mean you need to use "range" with your code?

It might actually be a better idea to take a step back. Can you explain your scenario (data and layout) and exactly what you are trying to do? There may be better ways of doing in VBA than trying to use VLOOKUP.

 

[lucki_87]

i have couple for columm of data

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">name </td> <td style="width:48pt" width="64">salary</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">john</td> <td align="right">45000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">bob</td> <td align="right">60000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">deryl</td> <td align="right">25000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">jean</td> <td align="right">15000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">marc</td> <td align="right">68000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">luke</td> <td align="right">95000</td> </tr> </tbody></table>

then i want to look up certain salary for certain people

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">name</td> <td style="width:48pt" width="64">salary</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">bob</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">deryl</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">luke</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> </tr> </tbody></table>what is the best macro code that can be used?

 

[Joe4]

That does seem like a pretty straightforward VLOOKUP macro situation. I am not sure why you need a macro instead of a straightforward VLOOKUP, but I trust you have your reasons.

One thing that will make your VLOOKUP formulas look a little cleaner is if you use a named range in your VLOOKUP formula instead of explicity declaring your range.

So here is how I would approach this:
1. Turn on your Macro Recorder
2. Name your lookup range (can probably use Current Region to select the range dynamically)
3. Enter the first VLOOKUP formula and copy down for all rows

Try this, and if you have problems or need help cleaning up/modifying your code, post the code here.

 

[lucki_87]

Range("A2:B7").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R2C1:R7C2,2)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E4"), Type:=xlFillDefault
Range("E2:E4").Select
Range("E6").Select

here is the sample code

but it seem very messy and ultimately i wanna compare two things to return a value

say if salary = $**** & job = title return person

 

[Joe4]

but it seem very messy and ultimately i wanna compare two things to return a value

Ah, so is that the real problem you have been having?
You didn't mention you were trying to match on two fields.

There are different ways you can do this. One of the easier ones is to add a "helper" field to combine the two fields together, and use in your VLOOKUP.

So, in your lookup table, insert a blank column to the left of your data. Now let's say you want to lookup the values in column B and C, and return the value from column D. If row 2 is your first row of data, then in cell A2 enter:
=B2 & C2
and copy all the way down. Include column A-D in your lookup range.

Does that make sense?

 

[lucki_87]

i am a little confused by what you mean

any example?

 

[Joe4]

OK. Let's say your data looks like below:

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=310><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 82pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 width=109>A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=73>C</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>D</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>Calc</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Title</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Salary</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>45000Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">john</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>45000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>60000Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">bob</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>60000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>25000Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">deryl</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>25000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>15000Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">jean</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Admin</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>15000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>68000Supervisor</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">marc</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Supervisor</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>68000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>95000Manager</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">luke</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Manager</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>95000</TD></TR></TBODY></TABLE>

Note that columns B, C, and D are you original columns, and in column A, I created the "combined" match by using the formula field:
=D2 & C2

Now, let's say the Salary value we want to look up is in cell G2 (i.e. 25000) and the Title value we want to look up is in cell H2 (i.e. Admin). Then, our VLOOKUP formula would look something like this:
=VLOOKUP(G2 & H2,A2:D7,2,0)