VBA Excel Index out of range -


i have written vba code converting csv file array , analysing in excel. code functioning of sudden getting error 1004, says method 'cells' object global failed. , highlighting line "cells(j, 15).value = strrow1(0)" below code. please if help

sub lithium() dim mydata string, strdata() string dim pathinit string dim integer dim z long, filecount long ' opening txt file dim mytxt mytxt = application.getopenfilename("csv files (*.csv), *.csv")   open mytxt binary #1 mydata = space$(lof(1)) #1, , mydata close #1 strdata() = split(mydata, vbcrlf)    dim strrow1() string  dim strrow2() string  dim strrow3() string  dim strrow4() string  dim strrow5() string  dim strrow6() string  dim ncount integer   ncount = 1  dim nrowlenth integer  nrowlenth = ubound(strdata) - lbound(strdata) ' length of total data   array   j = 3   = 16 nrowlenth             strrow1() = split(strdata(i), ";")              cells(j, 15).value = strrow1(0)            cells(j, 16).value = strrow1(1)            if cells(j, 16).value = "c0" cells(j, 17).value = strrow1(2)            if cells(j, 16).value = "c1" cells(j, 18).value = strrow1(2)            if cells(j, 16).value = "c4" cells(j, 19).value = strrow1(2)            cells(j, 21).value = left(cells(j, 17).value, 2)            cells(j, 22).value = left(cells(j, 18).value, 2)            cells(j, 23).value = right(left(cells(j, 18).value, 6), 2) & right(left(cells(j, 18).value, 4), 2)            cells(j, 23).numberformat = "0000"            cells(j, 24).value = left(cells(j, 19).value, 2)             cells(j, 26).value = right(left(cells(j, 19).value, 12), 2) & right(left(cells(j, 19).value, 10), 2)            cells(j, 27).value = right(left(cells(j, 19).value, 16), 2) & right(left(cells(j, 19).value, 14), 2)            if cells(j, 16).value = "c0" cells(j, 1).value = cells(j, 15).value else _            if cells(j, 16).value = "c1" cells(j, 1).value = cells(j, 15).value else _            if cells(j, 16).value = "c4" cells(j, 1).value = cells(j, 15).value else _            cells(j, 1).value = ""            if cells(j, 21).value = "" cells(j, 2).value = "#n/a" else cells(j, 2).value = clng("&h" & cells(j, 21).value)            if cells(j, 22).value = "" cells(j, 3).value = "#n/a" else cells(j, 3).value = clng("&h" & cells(j, 22).value)            if cells(j, 24).value = "" cells(j, 4).value = "#n/a" else cells(j, 4).value = clng("&h" & cells(j, 24).value) - 40            cells(j, 5).value = clng("&h" & cells(j, 25).value) - 40            if cells(j, 23).value = "" cells(j, 5).value = "#n/a" else cells(j, 5).value = clng("&h" & cells(j, 23).value) - 32768            if cells(j, 26).value = "" cells(j, 6).value = "#n/a" else cells(j, 6).value = clng("&h" & cells(j, 26).value)            if cells(j, 27).value = "" cells(j, 7).value = "#n/a" else cells(j, 7).value = clng("&h" & cells(j, 27).value)            if cells(j, 27).value = "" cells(j, 8).value = "#n/a" else cells(j, 8).value = cells(j, 6).value - cells(j, 7).value   j = j + 1  next  call copypastevalue call timetransformation   end sub

also on call function using above not functioning . when same small function code run separately not work main code. below call function codes

subcopypastevalue() sheets("database_1").range("a1:h500").copy sheets("database").range("a4").pastespecial _ paste:=xlpastevalues sheets ("database").columns("a").specialcells(x1celltypeblanks).entirerow.delete sheets("database").columns("a").autofilter field:=2, criteria1:="<>" end sub

and 

sub timetransformation()  dim l long dim lr long k = 2 lr = range("a" & rows.count).end(xlup).row  l = 2 lr  range("i" & k).value = val(left(right(range("a" & l).value, 12), 2)) + val(right(left(right(range("a" & l).value, 12), 5), 2)) / 60 + val(right(right(range("a" & l).value, 12), 6)) / 3600  k = k + 1  next  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