vlookup macro in excel

  • Thread starter Thread starter Midwest-Lisa
  • Start date Start date
M

Midwest-Lisa

I need some help with a simple macro. I need it to spin thru all the rows of
Sheet B, do a vlookup using project number to pick up project name from Sheet
A, and place the project name in Sheet B.

Sample information:

Sheet A - Project Overview
Col A = Project Number (lookup field, match with Sheet B, Col D)
Col B = Project Name (to be copied to Sheet B MoProject Name after the
macro is run)

Sheet B - Monthly Project Details

Col D = MoProject Number (lookup field - match with Sheet A, Col A)
Col E = MoProject Name (empty prior to the macro being run, contains
Project Name from Sheet A after macro is run)
 
Use worheetfunction.vlookup() when you are not looking for an exact amtch.
In your case where you are looking for exact matches then use find.

with sheets("Sheet B")
RowCount = 1
Do while .Range("D" & RowCount) <> ""
ProjNum = .Range("D" & RowCount)
with sheets("Sheet A")
set c = .Columns("A").find(what:=ProjNum,lookin:=xlvalues,
lookat:=xlwhole)
end with
if not c is nothing then
.Range("D" & RowCount) = c.offset(0,1)
end if
RowCount = RowCount + 1
loop
end with
 
Back
Top