excel - VBA Declare date/time data -


i ve got hourly data , when try execute following code runtime error 91.the format of data in cyc sheet example #07/07/2009 23:00:00# (at row 194), when enter dt automatically converts #7/7/2009 11:00:00 pm#. (please note shtcyc , shtco have been declared , set). 

dim dt date dt = #7/7/2009 11:00:00 pm#         shtcyc.activate     'finds day     set rng = shtcyc.range("a3:a1514").find(dt, , xlvalues)     'copies dates     shtcyc.range("a" & rng.row - 191 & ":a" & rng.row + 24).copy (this debug highlights)     shtco.range("b10").pastespecial paste:=xlpastevalues

anyone got ideas..? many many thanks!

well not problem see. see code below.

  1. to find dates have use datevalue because of various formatting reasons.
  2. you need check if value found
  3. you need check if rng.row falls in specific range

i have explained in comments. let me know if still have questions.

sub sample()     dim dt date     dim shtcyc worksheet     dim rng range      dt = #7/7/2009 11:00:00 pm#      set shtcyc = activesheet '<~~ change relevant sheet      shtcyc         set rng = .range("a3:a1514").find(what:=datevalue(dt), lookin:=xlformulas)          '~~> check if match found         if not rng nothing             '~~> check required because if rng.row <=191 or >=1048552?             '~~> understand have mentioned range "a3:a1514"             '~~> tom if use .cells then?             '~~> rng.row - 191 / rng.row + 24 give error in case             if rng.row > 191 or rng.row < (.rows.count - 24)                 .range("a" & rng.row - 191 & ":a" & rng.row + 24).copy                 'shtco.range("b10").pastespecial paste:=xlpastevalues             end if         else             msgbox "match not found"         end if     end end sub

tested in excel 2013. worksheet looks this.

enter image description here


-

Comments

Post a Comment

Popular posts from this blog

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more