Excel VBA vlookup using Dates -

i working three sheets. worksheet start page has dates a4 lastrow. have fund trend sheet dates a11 last row. vlookup searching dates in fund trend sheet based on list of dates in start page sheet. search table range in fund trend sheet range(a11:c11) lastrow. when date found offsets (3,0), , value presented sheet acurred expenses range("c7"). loop till lastrow in sheet start page a4.

=vlookup('start page'!a4,'fund trend'!a11:c21,3,0)

=vlookup('start page'!a5,'fund trend'!a12:c22,3,0)

as code have not been successful:

sub equity()   dim nav_date date   dim equity integer   nav_date = sheets("start page").range("a4")   equity = application.worksheetfunction.vlookup(nav_date,_  worksheets("fund trend").range("a11:c12"), 3, false)   sheets("acurred expenses").range("c7") = equity   end sub 

i think answer can broken down 3 parts: correctly referencing properties of range object, retrieving last row of data, , using loop

correctly referencing range's value:

---

the first thing noticed attempting assign date variable range object.

this line:

nav_date = sheets("start page").range("a4")

should be:

nav_date = sheets("start page").range("a4").value

a range object has properties , methods. must explicitly reference what range want get. it's value, it's cell address, etc.

likewise incorrect syntax repeated below. line:

sheets("acurred expenses").range("c7") = equity

should be:

sheets("acurred expenses").range("c7").value = equity

edit: per comments whytheq raises point of default properties. technically code sheets("acurred expenses").range("c7") = equity not incorrect, , work, because default property of range value. tend prefer more explicit, personal preference use range.value there not ambiguity. either way should work though!

retrieving last row of worksheet

---

to find last used row of data in worksheet, can start @ bottom of workbook , "look up" until find first row (which correspond last row of data in worksheet).

this code same activating last cell in column , them pressing ctrl+shit+↑

sub lastrow()      dim lrow long      lrow = cells(rows.count, 1).end(xlup).row      debug.print lrow  end sub 

to reiterate, starts @ bottom row , goes way up, returning row number of stops. corresponds last value entered in column a. might need change if data in different column.

the loop

---

finally, can put we've learned together. after have lrow corresponds last row in set of data can perform vlookup so:

sub equity()      dim nav_date date     dim equity integer     dim lrow long     dim long      lrow = sheets("start page").cells(rows.count, 1).end(xlup).row      = 4 lrow 'begin in row 4 of "start page" sheet         nav_date = sheets("start page").range("a" & i).value          'tell code continue if error occurs         on error resume next          equity = application.worksheetfunction.vlookup(nav_date, _          worksheets("fund trend").range("a11:c12"), 3, false)          'check results of vlookup, error number of 0 means no error         if err.number = 0             'here use i+3 because data started in row 7 , assume             'it offset 3 "start page"             sheets("acurred expenses").range("c" & + 3).value = equity         end if          'return normal error handling         on error goto 0      next  end sub