VLOOKUP in a Macro?


New Member
Jan 20, 2009
I'm working on a tracker and I have 2 sheets in this workbook: Tracker! and Lookup!. What I would like this macro to do is find the Agent Name in Column A in Lookup!, copy the data in columns B:E in that row, go to Tracker! and find the corresponding Agent Name in column A and paste the copied data in B:E of that sheet. I need to do this in one shot for multiple Agent Names (so look for "james", "sarah," "kenny," etc). I don't know if there is a way to do a vlookup or match in a macro.

The data in Lookup! will change daily so I would need it to paste the data in F:I the next time it's run and then J:M and so on.

Any help would be greatly appreciated.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.


MrExcel MVP, Moderator
May 28, 2005
Office Version
  1. 365
  1. Windows
See if this is headed in the right direction

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Tracker()<br>    <SPAN style="color:#00007F">Dim</SPAN> Tracker <SPAN style="color:#00007F">As</SPAN> Worksheet, Lookup <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> aName <SPAN style="color:#00007F">As</SPAN> Range, foundName <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LookupNames <SPAN style="color:#00007F">As</SPAN> Range, TrackerNames <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> NextCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Tracker = Sheets("Tracker"): <SPAN style="color:#00007F">Set</SPAN> Lookup = Sheets("Lookup")<br>    <SPAN style="color:#00007F">With</SPAN> Lookup<br>        <SPAN style="color:#00007F">Set</SPAN> LookupNames = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Tracker<br>        <SPAN style="color:#00007F">Set</SPAN> TrackerNames = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))<br>        NextCol = .Cells(1, .Columns.Count).<SPAN style="color:#00007F">End</SPAN>(xlToLeft).Column + 1<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> aName <SPAN style="color:#00007F">In</SPAN> LookupNames<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> foundName = TrackerNames.Find(What:=aName, After:=.Cells(1, 1), _<br>                LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> foundName <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                .Cells(foundName.Row, NextCol).Resize(, 4).Value = _<br>                    aName.Offset(, 1).Resize(, 4).Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> aName<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Tracker = Nothing: <SPAN style="color:#00007F">Set</SPAN> Lookup = Nothing<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back