VLookup Macro in Excel -

VLookup Macro in Excel -

i have excel workbook 2 worksheets. worksheet has several names in each name in different column , worksheet b contains same names in worksheet , sec column containing dates. example:

worksheet a. worksheet b. name. name. dates sean jake 11/13/15 jake sean 10/11/14 tom. chris 12/12/15

what trying set macro calls vlookup , passes name name column in worksheet search parameter on worksheet b. 1 time name found on worksheet b, returns date. manually having info pulled hard coding next vlookup in column on worksheet a.

=vlookup(a2,'worksheet b'!a:b,2,false)

any suggestions , help appreciated.

thank you.

you can utilize worksheet functions within vba. macro takes advantage of them returning values find appropriate cells.

sub auto_vlookup() dim rw long, wsb worksheet set wsb = worksheets("worksheet b") worksheets("worksheet a") rw = 2 .cells(rows.count, 1).end(xlup).row if cbool(application.countif(wsb.columns(1), .cells(rw, 1).value)) ' vlookup typically used homecoming info right of lookup column .cells(rw, 2) = application.vlookup(.cells(rw, 1).value, wsb.columns("a:b"), 2, false) ' index/match function pairs used wider scope .cells(rw, 3) = application.index(wsb.columns("n"), application.match(.cells(rw, 1).value, wsb.columns("a"), 0)) end if next rw .cells(2, 2).resize(rw - 2, 1).numberformat = "m/d/yyyy" end set wsb = nil end sub

you have edit worksheet names , adjust columns not same ones provided in sample data.

excel excel-vba