(Win xp - Excel 2003)


Hi all,

This code (on my machine) runs correctly until the variable myFigure
reaches 1.13 then just loops as if there is no cell value with 1.13.

It also does it at 1.38 if you start with the variable myFigure at more
than 1.13. Also at 1.63 if you start at 1.39.

This is confusing the hell out of me.

Can anyone shed light on this for me.

tia

Ron



Sub FirstRun()

Dim x As Integer
Dim myFigure As Double 'also tried just Dim myFigure
' Application.ScreenUpdating = False

myFigure = 1.05



Range("i3").Select

For x = 0 To 36

ActiveCell.Value = myFigure


Range("i2").Select

Do Until ActiveCell.Value = myFigure
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, 1).Value = "Line " & x
ActiveCell.Offset(1, 0).Select

myFigure = myFigure + 0.01

Next
End Sub

It appears that you are putting your incremental increase outside the loop
and you are selecting when you shouldn't.

If you are just trying to find 1.13 why use FIND instead. What are you
trying to do?

--
Don Guillett
SalesAid Software

"Ron" wrote in message
...
(Win xp - Excel 2003)


Hi all,

This code (on my machine) runs correctly until the variable myFigure
reaches 1.13 then just loops as if there is no cell value with 1.13.

It also does it at 1.38 if you start with the variable myFigure at more
than 1.13. Also at 1.63 if you start at 1.39.

This is confusing the hell out of me.

Can anyone shed light on this for me.

tia

Ron



Sub FirstRun()

Dim x As Integer
Dim myFigure As Double 'also tried just Dim myFigure
' Application.ScreenUpdating = False

myFigure = 1.05



Range("i3").Select

For x = 0 To 36

ActiveCell.Value = myFigure


Range("i2").Select

Do Until ActiveCell.Value = myFigure
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, 1).Value = "Line " & x
ActiveCell.Offset(1, 0).Select

myFigure = myFigure + 0.01

Next
End Sub

Hi Don,

It was/is part of a much more complex bit of code. I simplified it to
demonstrate the "error"

It doesn't matter what my aim is, I wanted to know if this is some sort of
glitch. Why does it just loop when it gets to 1.13? Why does it also
loop at 1.38, 1.63, 1.88, 2.02 and so on.

The code is simple enough, and works for so many times then just decides to
"down tools" at these same points every time.

I've looked at it time and again and can't see why it would run ok multiple
times and then just loop

Ron











"Don Guillett" wrote in
:

It appears that you are putting your incremental increase outside the
loop and you are selecting when you shouldn't.

If you are just trying to find 1.13 why use FIND instead. What are you
trying to do?

You might try:
Do Until Round(ActiveCell.Value,2) = myFigure
just in case your worksheet values have precision beyond what is being
displayed, thereby "not matching" myFigure.

or, rather than going for a 100% exact match, maybe go for "close enough".
Something like
Do Until abs(ActiveCell.Value - myFigure) =< 0.01

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"Ron" wrote in message
...
(Win xp - Excel 2003)


Hi all,

This code (on my machine) runs correctly until the variable myFigure
reaches 1.13 then just loops as if there is no cell value with 1.13.

It also does it at 1.38 if you start with the variable myFigure at more
than 1.13. Also at 1.63 if you start at 1.39.

This is confusing the hell out of me.

Can anyone shed light on this for me.

tia

Ron



Sub FirstRun()

Dim x As Integer
Dim myFigure As Double 'also tried just Dim myFigure
' Application.ScreenUpdating = False

myFigure = 1.05



Range("i3").Select

For x = 0 To 36

ActiveCell.Value = myFigure


Range("i2").Select

Do Until ActiveCell.Value = myFigure
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(0, 1).Value = "Line " & x
ActiveCell.Offset(1, 0).Select

myFigure = myFigure + 0.01

Next
End Sub

That's an interesting problem. I noted that the error did not occur when
myFigure was defined as either Single or Currency.

I also noted that the error also occurs if different increments are used.
For example, if the increment line is changed to
myFigure = myFigure + 0.005,
the error occurs when trying to find a match @ 1.06.

If myFigure = myFigure + 0.001,
the error occurs when trying to find a match @ 1.0525

I'm seeing a pattern here, but don't recognize it.

If myFigure = myFigure + 0.0001,
the error occurs when trying to find a match @ 1.055




"Ron" wrote:

Hi Don,

It was/is part of a much more complex bit of code. I simplified it to
demonstrate the "error"

It doesn't matter what my aim is, I wanted to know if this is some sort of
glitch. Why does it just loop when it gets to 1.13? Why does it also
loop at 1.38, 1.63, 1.88, 2.02 and so on.

The code is simple enough, and works for so many times then just decides to
"down tools" at these same points every time.

I've looked at it time and again and can't see why it would run ok multiple
times and then just loop

Ron











"Don Guillett" wrote in
:

It appears that you are putting your incremental increase outside the
loop and you are selecting when you shouldn't.

If you are just trying to find 1.13 why use FIND instead. What are you
trying to do?

Thanks George (& Don)


I know there are a few ways to do what I'm trying, this isn't why I
posted or what I want to know.

If we start with the variable myFigure at 1.05. The code runs and the
code generates the new myFigure variable. There is no input from
outside whatsoever during run time in this piece of code.

If the code, and only the code, generates the myFigure variable and then
places it in a cell, why cant it find it's own product in the next
instance of the loop?

It generates, and then subsequently finds it's own product upto and
including 1.12 but it then generates 1.13 and plonks the value in the
relevant cell. But here's my beef......why can't it look for and find
the value it has just created itself? regardless of absolute values or
decimal places?

Can anyone answer this?


Ron




















"George Nicholson" wrote in
:


You might try:
Do Until Round(ActiveCell.Value,2) = myFigure
just in case your worksheet values have precision beyond what is being
displayed, thereby "not matching" myFigure.

or, rather than going for a 100% exact match, maybe go for "close
enough". Something like
Do Until abs(ActiveCell.Value - myFigure) =< 0.01

HTH,

I suspect it has something to do with floating point error so that one of
your values is slightly off.



http://support.microsoft.com/default...kb;en-us;48606
XL: Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/default...kb;en-us;78113
XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default...kb;en-us;42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors


http://support.microsoft.com/default...b;en-us;165373
Rounding Errors In Visual Basic For Applications

http://support.microsoft.com/default...kb;en-us;69333
HOWTO: Work Around Floating-Point Accuracy/Comparison Problems


--
Regards,
Tom Ogilvy

"Ron" wrote in message
...
Thanks George (& Don)


I know there are a few ways to do what I'm trying, this isn't why I
posted or what I want to know.

If we start with the variable myFigure at 1.05. The code runs and the
code generates the new myFigure variable. There is no input from
outside whatsoever during run time in this piece of code.

If the code, and only the code, generates the myFigure variable and then
places it in a cell, why cant it find it's own product in the next
instance of the loop?

It generates, and then subsequently finds it's own product upto and
including 1.12 but it then generates 1.13 and plonks the value in the
relevant cell. But here's my beef......why can't it look for and find
the value it has just created itself? regardless of absolute values or
decimal places?

Can anyone answer this?


Ron




















"George Nicholson" wrote in
:


You might try:
Do Until Round(ActiveCell.Value,2) = myFigure
just in case your worksheet values have precision beyond what is being
displayed, thereby "not matching" myFigure.

or, rather than going for a 100% exact match, maybe go for "close
enough". Something like
Do Until abs(ActiveCell.Value - myFigure) =< 0.01

HTH,

"The value it created itself" - yes, that is the problem. Computers don't
create decimal numbers. Everything has to be converted from binary and that
is why typing of a variable can be critical. When you are testing
non-integer variables for exact matches you will always need to beware and
give your code some leeway to deal with floating point/rounding errors.
--
- K Dales


"Ron" wrote:

Thanks George (& Don)


I know there are a few ways to do what I'm trying, this isn't why I
posted or what I want to know.

If we start with the variable myFigure at 1.05. The code runs and the
code generates the new myFigure variable. There is no input from
outside whatsoever during run time in this piece of code.

If the code, and only the code, generates the myFigure variable and then
places it in a cell, why cant it find it's own product in the next
instance of the loop?

It generates, and then subsequently finds it's own product upto and
including 1.12 but it then generates 1.13 and plonks the value in the
relevant cell. But here's my beef......why can't it look for and find
the value it has just created itself? regardless of absolute values or
decimal places?

Can anyone answer this?


Ron




















"George Nicholson" wrote in
:


You might try:
Do Until Round(ActiveCell.Value,2) = myFigure
just in case your worksheet values have precision beyond what is being
displayed, thereby "not matching" myFigure.

or, rather than going for a 100% exact match, maybe go for "close
enough". Something like
Do Until abs(ActiveCell.Value - myFigure) =< 0.01

HTH,

Thanks all for the answers.

Thouroughly researched and understood now!

Ron

You still need to get rid of all the selections in your code.

--
Don Guillett
SalesAid Software

"Ron" wrote in message
...
Thanks all for the answers.

Thouroughly researched and understood now!

Ron

Hi Don,

Could you spare a minute to educate me on this? I'm always appreciative of
help and improvement.

Ron




"Don Guillett" wrote in news:#dsbshn2FHA.3880
@TK2MSFTNGP12.phx.gbl:

You still need to get rid of all the selections in your code.

Really busy with my son in the hospital for spinal surgery but

sheets("sheet1").select
range("a1").select
or
application.goto sheets("sheet1").range("a1")

range("a1").select
selection.copy
range("b1").select
selection.paste
or
range("a1").copy range("b1")
or
range("b1").value=range("a")

--
Don Guillett
SalesAid Software

"Ron" wrote in message
...
Hi Don,

Could you spare a minute to educate me on this? I'm always appreciative
of
help and improvement.

Ron




"Don Guillett" wrote in news:#dsbshn2FHA.3880
@TK2MSFTNGP12.phx.gbl:

You still need to get rid of all the selections in your code.

Thanks Don.

Really hope it goes as good as possible with your son.

I suffered a brachial plexus injury 6 years ago so I know surgery!

Best wishes Don.



Ron



















"Don Guillett" wrote in news:#9ctMr02FHA.3644
@TK2MSFTNGP09.phx.gbl:

Really busy with my son in the hospital for spinal surgery but

sheets("sheet1").select
range("a1").select
or
application.goto sheets("sheet1").range("a1")

range("a1").select
selection.copy
range("b1").select
selection.paste
or
range("a1").copy range("b1")
or
range("b1").value=range("a")