VLOOKUP in a Macro?

[jaclynresendez]

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.

 

[Peter_SSs]

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>