vba - Excel custom formula with IF statements and INDEX, MATCH returns a #VALUE error -


i extracting values 1 excel sheet another. use following formula: 

= if(isblank(ifna(index(range1;row;match(dates;range2;0));error1));error2;ifna(index(range1;row;match(dates;range2;0));error1))

since need calculations extracted data wanted create custom function simplify extraction process eliminating need type parameters stay same every time. built following function: 

function datacap(dates variant, row variant) variant     dim range1 range 'define whole lookup table        set range1 = range("data!a1:p151")     dim range2 range 'define date lookup table        set range2 = range("data!a3:p3")     dim error1 range 'define error 1; if no value        set error1 = range("x16")     dim error2 range 'define error 2; if blank        set error2 = range("y16")     datacap.formula "= if(isblank(ifna(index(range1;row;match(dates;range2;0));error1));error2;ifna(index(range1;row;match(dates;range2;0));error1))"   end function

when run #value error. if paste equation (with same arguments) directly in cell, works expected. so, think problem vba not able calculation. suggestions how fix this?

thank in advance

without being able test on data can't sure if work, might @ least able point more in right direction.

public function dcap(xdates variant, xrow variant) dim range1 range, range2 range, error1 range, error2 range  set range1 = activeworkbook.sheets("data").range("a1:p151") set range2 = activeworkbook.sheets("data").range("a3:p3")  set error1 = activeworkbook.activesheet.range("x16") set error2 = activeworkbook.activesheet.range("y16")  on error resume next if iserror(worksheetfunction.index(range1, xrow, worksheetfunction.match(xdates, range2, 0)))     dcap = error1 elseif len(worksheetfunction.index(range1, xrow, worksheetfunction.match(xdates, range2, 0))) = 0     dcap = error2 else     dcap = worksheetfunction.index(range1, xrow, worksheetfunction.match(xdates, range2, 0)) end if on error goto 0  end function

Comments

Post a Comment

Popular posts from this blog

python - Operations inside variables -

this question has answer here: evaluating mathematical expression in string 12 answers i want make python script finds maximum possible output using 2 operations. problem says there syntax error @ 2nd last line, in b in there. how can fix it? x = 0 = int(input("1st number:")) c = int(input("2nd number:" )) e = int(input("3rd number:" )) in range(4): if == 0: b = "+" elif == 1: b = "-" elif == 2: b = "/" else: b = "*" j in range(4): if j == 0: d = "+" elif j == 1: d = "-" elif j == 2: d = "/" else: d = "*" k = b c d e print(k) if you're after variables e on single line, couldn...
Read more

Generic Map Parameter java -

i having lot of methods of type: public static list<eduusers> getdetails(class c, map<string, integer> params) { list<eduusers> details = null; session session = hibernateutil.getsessionfactory().opensession(); transaction tx = null; try { tx = session.begintransaction(); criteria cr = session.createcriteria(c); (map.entry<string, integer> entry : params.entryset()) { cr.add(restrictions.eq(entry.getkey(), entry.getvalue())); } details = cr.list(); tx.commit(); } catch (exception asd) { log.debug(asd.getmessage()); if (tx != null) { tx.commit(); } } { session.close(); } return details; } i trying have generic method them , have written far: public static <t> list<t> getdetails(class<t> c, class<t> b) { l...
Read more

arrays - What causes a java.lang.ArrayIndexOutOfBoundsException and how do I prevent it? -

what arrayindexoutofboundsexception mean , how rid of it? here code sample triggers exception: string[] name = {"tom", "dick", "harry"}; for(int = 0; i<=name.length; i++) { system.out.print(name[i] +'\n'); } your first port of call should documentation explains reasonably clearly: thrown indicate array has been accessed illegal index. index either negative or greater or equal size of array. so example: int[] array = new int[5]; int boom = array[10]; // throws exception as how avoid it... um, don't that. careful array indexes. one problem people run thinking arrays 1-indexed, e.g. int[] array = new int[5]; // ... populate array here ... (int index = 1; index <= array.length; index++) { system.out.println(array[index]); } that miss out first element (index 0) , throw exception when index 5. valid indexes here 0-4 inclusive. correct, idiomatic for statement here be: for (int index = 0; index <...
Read more