excel - Repeat part of my sub -


i'd run part of sub, different keyword each time it's run. i'm writing this, there shorter way doing it?

i'm making category direct debits, atm cash withdrawals, , debit card purchases in column d - searching d/d, c/l, , pos respectively in column b.

i'm changing variables, typing , running same bit of code every time. feel there should way "run bit again, searchterm , searchresult changed!"

i'm sure more knowledgable help. i'm using sub can't insert sub run within it, or function? , i'm looping i'm not sure how loop fit in?

what do? can guess i'm quite new this, little js knowledge in past.

sub organisedefaultcategories()     '     ' organisedefaultcategories macro     ' categorise bank statement entries default inputs. run first.     '      dim foundrange range, firstaddress string, searchterm variant, searchresult variant      searchterm = "d/d"     searchresult = "direct debit"          range("b:b")       set foundrange = .find(what:=searchterm, lookin:=xlvalues, _         lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=false)       firstaddress = foundrange.address               foundrange.offset(0, 2).value2 = searchresult          set foundrange = .findnext(foundrange)       loop while not foundrange nothing , foundrange.address <> firstaddress        searchterm = "c/l"     searchresult = "atm cash withdrawal"         set foundrange = .find(what:=searchterm, lookin:=xlvalues, _         lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=false)       firstaddress = foundrange.address               foundrange.offset(0, 2).value2 = searchresult          set foundrange = .findnext(foundrange)       loop while not foundrange nothing , foundrange.address <> firstaddress        searchterm = "pos"     searchresult = "debit card purchase"         set foundrange = .find(what:=searchterm, lookin:=xlvalues, _         lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=false)       firstaddress = foundrange.address               foundrange.offset(0, 2).value2 = searchresult          set foundrange = .findnext(foundrange)       loop while not foundrange nothing , foundrange.address <> firstaddress      end       end sub

give shot:

option explicit  sub organisedefaultcategories()   '   ' organisedefaultcategories macro   ' categorise bank statement entries default inputs. run first.   '    dim searchterm string   dim searchresult string    searchterm = "d/d"   searchresult = "direct debit"   finder searchterm, searchresult   searchterm = "c/l"   searchresult = "atm cash withdrawal"   finder searchterm, searchresult   searchterm = "pos"   searchresult = "debit card purchase"   finder searchterm, searchresult  end sub  sub finder(byval searchterm string, byval searchresult string)    dim foundrange range   dim firstaddress string    range("b:b")     set foundrange = .find(what:=searchterm, lookin:=xlvalues, _                            lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _                            matchcase:=false, searchformat:=false)     if not foundrange nothing       firstaddress = foundrange.address               foundrange.offset(0, 2).value2 = searchresult         set foundrange = .findnext(foundrange)       loop while not foundrange nothing , foundrange.address <> firstaddress     end if   end  end sub

i created small procedure finder takes 2 parameters, searchterm , searchresult, set variables , call finder each pair.

i declared 2 string instead of variant. want use variant when absolutely necessary (some functions require variant) because can lead hard find errors, , it's slower execute.

i modified code when extracted finder procedure ensure check result of .find ensure have (i.e. not ... nothing) before attempting use in way. vba not short circuit if statements, if didn't find .find, have still received error way had written.

congrats on using .find instead of looping through rows making comparison! .find faster.


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more