i working 3 excel sheets. in sheet start page have dates starting column a4 going down. macro vlooks in sheet fund trend same dates locate in column a11 lastrow , offsets 3 columns , , copies value sheet "accrued expenses" starting range("c7"). macro loops until lastrow in sheets("start page") range("a4") .
the problem macro not populating values sheet accrued expenses, on occasions. or not finding date. code below:
sub netasset_value() dim result double dim nav_date worksheet dim fund_trend worksheet dim lrow long dim long set nav_date = sheets("start page") set fund_trend = sheets("fund trend") lrow = sheets("start page").cells(rows.count, 1).end(xlup).row = 4 lrow result = application.worksheetfunction.vlookup(nav_date.range("a" & i), fund_trend.range("a11:c1544"), 3, false) sheets("accrued expenses").range("c" & + 3).value = result sheets("accrued expenses").range("c" & + 3).numberformat = "0.00" sheets("accrued expenses").range("c" & + 3).style = "comma" next end sub error trap:
on error resume next result = application.worksheetfunction.vlookup(nav_date.range("a" & i), fund_trend.range("a11:c1544"), 3, false) if err.number = 0 sheets("accrued expenses").range("c" & + 3).value = result sheets("accrued expenses").range("c" & + 3).numberformat = "0.00" sheets("accrued expenses").range("c" & + 3).style = "comma" end if on error goto 0 to on come date issue have sub dont know if efficient?
sub dates() sheets("start page").range("a4", "a50000").numberformat = "dd-mm-yyyy" sheets("fund trend").range("a11", "a50000").numberformat = "dd-mm-yyyy" end sub the issue having when enter date 11/02/2015 switches 02/11/2015. not happening dates
overcoming problem. placed worksheet function force date columns text. working.
private sub worksheet_selectionchange(byval target range) sheets("start page").range("a4", "a50000").numberformat = "@" sheets("fund trend").range("a11", "a50000").numberformat = "@" end sub
to avoid 1004 error can use application.vlookup function, allows error type return value. use method test error, , if no error, return result.
to this, you'll have dim result variant since (in example) put text/string value in result identify error occurrences.
if iserror(application.vlookup(nav_date.range("a" & i), fund_trend.range("a11:c1544"), 3, false)) result = "date not found!" else result = application.worksheetfunction.vlookup(nav_date.range("a" & i), fund_trend.range("a11:c1544"), 3, false) end if the "no result printed in worksheet" needs further debugging on end. have stepped through code ensure result expect be, given lookup value? if there no error, certainly happening formula have entered returning null string , value being put in cell.
