Excel-Vba - Double/Date/Time matching and tolerance -


my code far this:

sub findmatchingvalue()     dim integer, timevaluetofind date     timevaluetofind = "04:00:00"     sheets("vessels").range("f07").clearcontents     = 1 25   '         if cdate(sheets("vessels").cells(i, 1).value) = timevaluetofind             msgbox ("found value on row " & i)              sheets("vessels").range("f07").value = cells(i, 1).offset(1, 1).resize(1).value             exit sub         end if     next     msgbox ("value not found in range!") end sub 

this code checks column time inputted in format xx:xx:xx both input is, , times written set "time" format. cdate edit not added. , caused code return false because, had been put, trying "compare apples oranges".

however adding cdate addition produces mismatch error. changing both double did not work:

sub findmatchingvalue()     dim integer, timevaluetofind date     timevaluetofind = "04:00:00"     sheets("vessels").range("f07").clearcontents     = 1 25   '         if sheets("vessels").cells(i, 1).value = cdbl(timevaluetofind)  ' < line changed             msgbox ("found value on row " & i)              sheets("vessels").range("f07").value = cells(i, 1).offset(1, 1).resize(1).value             exit sub         end if     next     msgbox ("value not found in range!") end sub 

however 1 different reason, since excel stores values floating points, each value still different. "it known expression a==b return false when , b both doubles, though might think same. due finite precision floating point numbers stored."

the way around set tolerance. if abs(a-b)<tolerance then i'm not particularly sure tolerance use nor how write include without messing first loop.
wonder if shed light on , direct me additions need make , sort of tolerances acceptable? think question twofold. thank in advance!

use timevalue() or timeserial() so:

sub so()  dim x date dim y date dim z date   x = timevalue("04:00:00") y = timeserial(4, 0, 0) z = cdate(range("a1").value) '// a1 has "04:00:00" entered  debug.print x = y '// true debug.print y = z '// true debug.print x = z '// true  end sub 

putting context of code:

sub findmatchingvalue()     dim integer, timevaluetofind date     timevaluetofind = timevalue("04:00:00")     sheets("vessels").range("f07").clearcontents     = 1 25   '         if cdate(sheets("vessels").cells(i, 1).value) = timevaluetofind             msgbox ("found value on row " & i)             sheets("vessels").range("f07").value = cells(i, 1).offset(1, 1).resize(1).value             exit sub         end if     next     msgbox ("value not found in range!") end sub 

Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -