vba - Infinite loop with .Find method -


i trying write vba script automate moving things around in spreadsheet has balance sheet imported accounting software. values on imported balance sheet start @ row 5, column has text describing values of each row mean, , columns b , d have amounts each item.

the subtotals each section , subsection of balance sheet on columns c , e. each subtotal has in cell formatted solid upper border.

i bring these subtotals same columns values (i.e, columns b , d). i've tried using .find method search cells specific format (cells upper border) , loop keep searching until find cells should have subtotal in it.

notes:

  1. i didn't use findnext because seems ignores format settings used in preceding find method, described here.
  2. i tried used findall function described tushar mehta go around problem findnext, didn't find cells specified format.

here's code. appreciated!

sub fixbalancesheet()   dim lookfor range   dim foundhere string 'address of cell should contain subtotal   dim beginat range, endat range, rng range 'set ranges sum subtotal   dim place string 'string address of cell contain subtotal   dim wheretolook range 'range subtotals found    'set workbook , worksheet   sheets("sheet1")     set wheretolook = range("a5:f100")     'every cell containing subtotal has upper border. so, cells containing border!     application.findformat.borders(xledgebottom)         .linestyle = xlcontinuous         .colorindex = xlautomatic         .tintandshade = 0     end     'call search using .find     set lookfor = wheretolook.find(what:="", after:=cells(5, 2), _         lookin:=xlformulas, lookat:=xlpart, _         searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=true)     if not lookfor nothing 'test if cell bottom border found       'what happens when subtotal cell found:       foundhere = lookfor.address       debug.print "found at: " & found       'loop set range, sum values , put them in right cell              '% find out range calculate subtotals , put value in right cells  %'         'call next search         application.findformat.borders(xledgebottom)         .linestyle = xlcontinuous         .colorindex = xlautomatic         .tintandshade = 0         end         set lookfor = wheretolook.find(what:="", after:=endat, searchformat:=true)         debug.print "lookfor is: " & lookfor.address         rem if lookfor.address = found ' not allow wrapped search           rem exit         rem end if       loop until lookfor nothing or lookfor.address = foundhere ' not allow wrapped search     end if   end end sub

consider using range object loop through range. can add total if need grand total, easier way trying select cells have formatting.

for example:

sub teststackoverflowcode()     dim r range     dim rngtochk range      'this you'd insert wheretolook     set rngtochk = activesheet.range("b1:b4")      each r in rngtochk          'if top edge not not have border         if r.borders(xledgetop).linestyle <> xlnone             'copy cell value 2 cells right             r.offset(, 2).value = r.value         end if     next r  end sub

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