excel vba - VBA FormulaR1C1 adds parentheses when HLOOKUP referances another workbook -


i'm using following code variable workbook , retrieve data. enters formula cell range grabs data out of other workbook. problem if use r1c1 style in formula indicate range in other workbook want search, vba enters formula if a1 style. formula not return correct info.

but if leave portion of code in a1 style, vba adds parentheses range when enters cell , formula fails.

here line of code:

range("r8:r" & finalrow).formular1c1 = "=iferror(hlookup(if(right(rc[-13],2)=""b4"",index('[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]" & myua _                 & "'!d:e,match(rc4,'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]" & myua & "'!e:e,0),1)," _                 & "index(indirect(""'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]""&rc5&""'!d:e"",true)," _                 & "match(rc4,indirect(""'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]""&rc5&""'!e:e"",true),0),1))" _                 & ",'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]sop'!r1c3:r4c41,4,false),""np"")"

here result:

=iferror(hlookup(if(right(e13,2)="b4",index('[5p1b 4520 5015 evms.xlsm]ua1b'!d:(e),match($d13,'[5p1b 4520 5015 evms.xlsm]ua1b'!e:(e),0),1),index(indirect("'[5p1b 4520 5015 evms.xlsm]"&$e13&"'!d:e",true),match($d13,indirect("'[5p1b 4520 5015 evms.xlsm]"&$e13&"'!e:e",true),0),1)),'[5p1b 4520 5015 evms.xlsm]sop'!$c$1:$ao$4,4,false),"np")

i see you're using indirect in formula, , work if referenced workbook open. if open, can make whole formula indirect , rid of hassles way. can referencing address(row()-x,column()-y) within formulas work r1c1 style formula, , reference different rows/columns depending on own position though formula identical in every cell. example of simple formula works way this:
=indirect("'[my workbook.xlsx]sheet1'!"&address(row()-3,column()-1))

i believe need:

range("r8:r" & finalrow).formula = "=iferror(hlookup(if(right(indirect(address(row()-13,column()+2)))=""b4"",index('[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]" & myua _             & "'!d:e,match(indirect(address(row(),column()+4)),'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]" & myua & "'!e:e,0),1)," _             & "index(indirect(""'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]""&rc5&""'!d:e"",true)," _             & "match(indirect(address(row(),column()+4)),indirect(""'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]""&rc5&""'!e:e"",true),0),1))" _             & ",'[" & mynm & " " & mycc & " " & myunit & " evms.xlsm]sop'!indirect(address(row()+1,column()+3 &":"& address(row()+4, column()+41)),4,false),""np"")"

Comments

Post a Comment

Popular posts from this blog

1111. appearing after print sequence - php -

morning, i have code takes string of shoe sizes, explodes , puts in table. code works , info displayed correctly on page after table there few 1's after table. i'm confused this. so table looks (in columns of 3 on webpage) available in following sizes: 3 4 5 6 9 10 111 here's codes, purpose of example $x = 3|4|6|10|9 function get_sizes_pipe($x) { $counter = 0; $splits = explode("|",$x); asort($splits); $x = print "<table class='greentable'>"; $x .= print "<thead><tr><th scope='col' colspan='3' abbr='starter'>available in following sizes:</th></tr></thead><tbody><tr>"; foreach ($splits $split) { $entry = print "<td>".$split."</td>"; if($counter == 2){ $entry .= print "</tr><tr>"; $counter =0; } else { $counter++; ...
Read more

java - WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/board/] in DispatcherServlet with name 'appServlet' -

this question has answer here: why spring mvc respond 404 , report “no mapping found http request uri […] in dispatcherservlet”? 3 answers i trying making simple bbs i'm encountering following error, warn : org.springframework.web.servlet.pagenotfound - no mapping found http request uri [/board/] in dispatcherservlet name 'appservlet' i have tried solve error many times, have failed. how can solve error? here web.xml, controllerand , servlet-context. my web.xml <?xml version="1.0" encoding="utf-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <filter> <filter-nam...
Read more

Ruby on Rails, ActiveRecord, Postgres, UTF-8 and ASCII-8BIT encodings -

weirdest error, who’s challenge try , me? spent hours yesterday on , it’s magic. can't believe happening , it's driving me crazy. btw, using: rails 4.2.3 ruby 2.2.3 pg 0.18 encoding.default_internal = encoding.default_external = utf-8 config.encoding = 'utf-8' it has postgres, activerecord , encodings! turns out we’ve been getting these errors whenever create new users on database special characters: encoding::undefinedconversionerror: "\xc3" ascii-8bit utf-8 \xc3 may vary depending on characters. weird part we’ve set since beginning utf-8. makes no sense , digged it, , used script: user.all.each |user| user.attributes.each |name, value| if value.is_a? string puts user.email + name.encoding.to_s + value.encoding.to_s end end end and output of 1 of users, users same: dr_lottahelp@blahblah.comutf-8utf-8 dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8asci...
Read more