Delete negative number with decimal

[somenoob]

Hi. I am having a problem. I have this codes

Dim threshold As Long
threshold = 0.5
Dim negativeThreshold As Long
negativeThreshold = 0
Dim logic As String
logic = (ws.Range("C" & x) = "check") And Val(ws.Range("D" & x))

If logic > threshold Or logic < negativeThreshold Then
ws.Range("E" & x).Value = "->Chk_" & counter
counter = counter + 1
Else
ws.Range("E" & x).Value = ""
End If

Next x

i want to check for values lesser than 0. As in negative values. But it can get -1. but not -0.1

please help
<!-- / message -->

 

[Richard Schollar]

logic (dimensioned as a string, although I'm not sure why) is only going to hold a 1 or a 0 due to the logic statement assigning it a value. What are you hoping to achieve here?

 

[somenoob]

i see. i have change it. Thanks

I have a question to ask.

<TABLE style="WIDTH: 87pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=116 x:str><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 3299" width=116><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; WIDTH: 87pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20 width=116 align=right x:num="94610.15">94,610.15 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20 align=right x:num="94610.15">94,610.15 </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=21 x:num="-1.4551915228366852E-11"> (0.00)</TD></TR></TBODY></TABLE>

i have one part in my excel that display the above. it means that 94,610.15 - 94,610.15 will give me 0. but why is it that the 0 is displayed as (0.00)? when i click on the cell, it shows
-1.45519152283669E-11
it should be 0.

 

[Richard Schollar]

That sounds like a floating point arithmetic problem as a result of binary storage of numbers:

http://support.microsoft.com/kb/78113/en-us

 

[somenoob]

I see. So how do i solve it? do i round the decimals or some other way?

 

[Richard Schollar]

Yes, you can use the Round() function:

 dblResult = Round(dblVal1 - dblVal2,2)

where dblResult, dblVal1 and dblVal2 are decimal (Double) variables and the 2 on the end is the number of decimal places you want to round to (adjust as required).