excel - Use VBA to replace sporadic #N/A cells with a formula -


i have data comes different sources via data/connections goes sheet/table called rawdata. have created macro linked button user must click fill in of data in rawdata index/match function points workbook reservation number rawdata matched other workbook determine length of stay.

i not appending said workbook rawdata table different reasons. can go route if need figured there easier route before cross bridge. issue have once macro ran, other workbook, @ times, may not have reservation data. in these cases, had manually determine data.

this new data can placed within main workbook [but have in it's own workbook (losnaintoactuallos.xlsm) i've been running tests on making work]. , formula has pasted table because when table refreshes, row of data have length of stay removes formula , replaces it's original value, blank cell.

what need code loop through cells within f column of table, determine cells #n/a errors, paste formula in error cell. have done research , can't seem find suits needs. attempted doing loop seeing i'm still pretty new vba coding, it's not strong suit. 

sub macro2() dim r integer lastrow = range("b" & rows.count).end(xlup).row  worksheets("sheet1").range("f2:f" & lastrow) r = 2 lastrow   if .iserror(worksheets("sheet1").range("f" & r)).value     .formula = "index(losnaintoactuallos.xlsm!table1[#data],match([@reservation],losnaintoactuallos.xlsm!table1[reservation],0),7)"     .value = .value   end if next r end end sub

when run code if statement run-time error '438': object doesn't support property or method. if remove first period .iserror compile error: invalid qualifier.

iserror not part of worksheets("sheet1").range("f2:f" & lastrow)

switch this.

sub macro2() dim r long dim lastrow long lastrow = worksheets("sheet1").range("b" & rows.count).end(xlup).row   r = 2 lastrow     worksheets("sheet1").range("f" & r)         if iserror(.value)             .formula = "index(losnaintoactuallos.xlsm!table1[#data],match([@reservation],losnaintoactuallos.xlsm!table1[reservation],0),7)"             .value = .value         end if     end next r  end sub

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more