using decimal numbers in for-next statement
 

I'm using a for-next statement for finding values in certain ranges.

When I use whole numbers, there is no problem and everything work
allright. But in one range I need to beable to use decimal numbers, an
suddenly my code seems to contain errors. Here is the working code:

If Not pro1.text = "" Then
fa = pro1.text
fb = pro2.text
For x = fa To fb
For y = 1 To 5
Set c = Range("F2:F2000").Find(fa, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 7
c.Offset(0, -1).Interior.ColorIndex = 7
c.Offset(0, -2).Interior.ColorIndex = 7
c.Offset(0, -3).Interior.ColorIndex = 7
c.Offset(0, -4).Interior.ColorIndex = 7
c.Offset(0, -5).Interior.ColorIndex = 7
End If
y = y + 1
Next y
fa = fa + 1
Next x
End If

And the code that doesn't work:

If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb
For y = 1 To 5
Set c = Range("G2:G2000").Find(ga, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
y = y + 1
Next y
ga = ga + 0.01
Next x
End If

As you can clearly see, the only thing I've changed is the interval fo
the next value of x.
I've spend the last three hours coming up with solutions, but nothin
is working.
I'm hoping one of you can see one?!

tnx in advance,
Pee

--
Message posted from http://www.ExcelForum.com

using decimal numbers in for-next statement
 

Hi

Intervals should not be set and inside a a loop, and counter values should
not be changed inside a a loop, provide everything in the For statement:

Sub TestLoops()
Dim D As Double
Dim L As Long

For D = 0.1 To 1.3 Step 0.1
For L = 1 To 3
Debug.Print D, L
Next
Next
End Sub

HTH. best wishes Harald

"Peer " skrev i melding
...
I'm using a for-next statement for finding values in certain ranges.

When I use whole numbers, there is no problem and everything works
allright. But in one range I need to beable to use decimal numbers, and
suddenly my code seems to contain errors. Here is the working code:

If Not pro1.text = "" Then
fa = pro1.text
fb = pro2.text
For x = fa To fb
For y = 1 To 5
Set c = Range("F2:F2000").Find(fa, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 7
c.Offset(0, -1).Interior.ColorIndex = 7
c.Offset(0, -2).Interior.ColorIndex = 7
c.Offset(0, -3).Interior.ColorIndex = 7
c.Offset(0, -4).Interior.ColorIndex = 7
c.Offset(0, -5).Interior.ColorIndex = 7
End If
y = y + 1
Next y
fa = fa + 1
Next x
End If

And the code that doesn't work:

If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb
For y = 1 To 5
Set c = Range("G2:G2000").Find(ga, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
y = y + 1
Next y
ga = ga + 0.01
Next x
End If

As you can clearly see, the only thing I've changed is the interval for
the next value of x.
I've spend the last three hours coming up with solutions, but nothing
is working.
I'm hoping one of you can see one?!

tnx in advance,
Peer


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

Not sure your code is working as you think?

ga = 10
gb = 12
for x = ga to gb : debug.Print x: ga = ga + 0.1 : next
10
11
12

perhaps you want

for x = ga to gb step 0.01

for x = ga to gb step 0.1 : debug.Print x : next
10.3
10.4
10.5
10.6
10.7
10.8
10.9
11
11.1
11.2
11.3
11.4
11.5
11.6
11.7
11.8
11.9
12

--
Regards,
Tom Ogilvy



"Peer " wrote in message
...
I'm using a for-next statement for finding values in certain ranges.

When I use whole numbers, there is no problem and everything works
allright. But in one range I need to beable to use decimal numbers, and
suddenly my code seems to contain errors. Here is the working code:

If Not pro1.text = "" Then
fa = pro1.text
fb = pro2.text
For x = fa To fb
For y = 1 To 5
Set c = Range("F2:F2000").Find(fa, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 7
c.Offset(0, -1).Interior.ColorIndex = 7
c.Offset(0, -2).Interior.ColorIndex = 7
c.Offset(0, -3).Interior.ColorIndex = 7
c.Offset(0, -4).Interior.ColorIndex = 7
c.Offset(0, -5).Interior.ColorIndex = 7
End If
y = y + 1
Next y
fa = fa + 1
Next x
End If

And the code that doesn't work:

If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb
For y = 1 To 5
Set c = Range("G2:G2000").Find(ga, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
y = y + 1
Next y
ga = ga + 0.01
Next x
End If

As you can clearly see, the only thing I've changed is the interval for
the next value of x.
I've spend the last three hours coming up with solutions, but nothing
is working.
I'm hoping one of you can see one?!

tnx in advance,
Peer


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

tnx, guys

But it still won't work!

Even using for..step..next the whole numbers work, and decimal number
won't!
Maybe it has something to do with my country settings. I'm living i
holland and we write decimal numbers like this:
xx,yy (10,00 for example)
But I tried using country settings for UK (10.00 for example) - n
effect!
Any other suggestions?

P.S. now my code is:
If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb Step 0.01
Debug.Print x
For y = 1 To 5 Step 1
Set c = Range("G2:G500").Find(x, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
Next y
Next x
End I

--
Message posted from http://www.ExcelForum.com

using decimal numbers in for-next statement
 

Hi

Which datatypes are ga and gb declared as ? I'd think the problems lie he
ga = ppe1.text
gb = ppe2.text
does it work with

For x = 9 To 11 Step 0.01

? Also, turn off any error handlers (like on error resume next) and see what
happens.

Best wishes Harald

"Peer " skrev i melding
...
tnx, guys

But it still won't work!

Even using for..step..next the whole numbers work, and decimal numbers
won't!
Maybe it has something to do with my country settings. I'm living in
holland and we write decimal numbers like this:
xx,yy (10,00 for example)
But I tried using country settings for UK (10.00 for example) - no
effect!
Any other suggestions?

P.S. now my code is:
If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb Step 0.01
Debug.Print x
For y = 1 To 5 Step 1
Set c = Range("G2:G500").Find(x, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
Next y
Next x
End If


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

in addition to Harald's advice, decimal numbers are not always stored
exactly. For example, if you format the cell for two decimal places, it
might appear as
2.43

but actually be stored as 2.429999999 (same type thing if the value is
produced by a formula).

So there may not actually be an exact match.

--
Regards,
Tom Ogilvy

"Peer " wrote in message
...
tnx, guys

But it still won't work!

Even using for..step..next the whole numbers work, and decimal numbers
won't!
Maybe it has something to do with my country settings. I'm living in
holland and we write decimal numbers like this:
xx,yy (10,00 for example)
But I tried using country settings for UK (10.00 for example) - no
effect!
Any other suggestions?

P.S. now my code is:
If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb Step 0.01
Debug.Print x
For y = 1 To 5 Step 1
Set c = Range("G2:G500").Find(x, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
Next y
Next x
End If


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

I have tried every datatype it could possibly be;

integer, long, single, double, even variant after which I define
decimal numbers with cdec.

It was the first solution (or error) I thought of.

And the numbers are exactly the numbers I'm searching for, beause ther
are no formula's in the range, and the values are manual input.

:confused

--
Message posted from http://www.ExcelForum.com

using decimal numbers in for-next statement
 

Can you find the values manually using Edit=find. If so, then you should
be able to do it with the Find method. If you can't, then you probably
can't with the find method either. cdec is certainly on the answer.

--
Regards,
Tom Ogilvy

"Peer " wrote in message
...
I have tried every datatype it could possibly be;

integer, long, single, double, even variant after which I defined
decimal numbers with cdec.

It was the first solution (or error) I thought of.

And the numbers are exactly the numbers I'm searching for, beause there
are no formula's in the range, and the values are manual input.

:confused:


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

Set c = Range("G2:G500").Find(x, ActiveCell,...

Is "ActiveCell" within the range of G2:G500?

Consider the "Currency" data type also when working with numbers with 2
decimal places.

Dana DeLouis

"Peer " wrote in message
...
tnx, guys

But it still won't work!

Even using for..step..next the whole numbers work, and decimal numbers
won't!
Maybe it has something to do with my country settings. I'm living in
holland and we write decimal numbers like this:
xx,yy (10,00 for example)
But I tried using country settings for UK (10.00 for example) - no
effect!
Any other suggestions?

P.S. now my code is:
If Not ppe1.text = "" Then
ga = ppe1.text
gb = ppe2.text
For x = ga To gb Step 0.01
Debug.Print x
For y = 1 To 5 Step 1
Set c = Range("G2:G500").Find(x, ActiveCell, xlValues, _
xlWhole, xlByColumns, xlNext)
If Not c Is Nothing Then
c.Activate
c.Interior.ColorIndex = 8
c.Offset(0, -1).Interior.ColorIndex = 8
c.Offset(0, -2).Interior.ColorIndex = 8
c.Offset(0, -3).Interior.ColorIndex = 8
c.Offset(0, -4).Interior.ColorIndex = 8
c.Offset(0, -5).Interior.ColorIndex = 8
c.Offset(0, -6).Interior.ColorIndex = 8
End If
Next y
Next x
End If


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

Tom's comment about precision loss in cells also applies to VBA. Try
this simple test. In the Immediate Window type in

for i=2.00 to 3.00 step 0.01:debug.Print i:next i

Upto 2.23 everything is as expected. Then, instead of 2.24, I get
2.23999999999999. By contrast, the following gave accurate results
from 2 to 3:

For xStep=0 to (3-2)/0.01:x=2+xStep*0.01:debug.Print x:next xstep

So, you may want to try:

dim xStep as integer,x as double
'might need long rather than integer
For xStep=0 to (gb-ga)/0.01
x=ga+xStep*0.01
...
next xStep

Also, when you share code, it would help if you included the variable
declaration statements.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , Peer
says...
I have tried every datatype it could possibly be;

integer, long, single, double, even variant after which I defined
decimal numbers with cdec.

It was the first solution (or error) I thought of.

And the numbers are exactly the numbers I'm searching for, beause there
are no formula's in the range, and the values are manual input.

:confused:


---
Message posted from http://www.ExcelForum.com/

using decimal numbers in for-next statement
 

Tnx very much, guys!

After the comment of Tom I applied a teller to see which values wer
created by the loop, and indeed the values differed from what the
should by 0.00000001, or something like that :D
And the solution provided Tushar works perfectly!

Again tnx a lot

--
Message posted from http://www.ExcelForum.com

using decimal numbers in for-next statement
 

Just another option in the immediate window (use of @)

For i = 2 To 3 Step 0.01@: Debug.Print i: Next i

'or

Sub Demo()
Dim i As Currency
For i = 2 To 3 Step 0.01: Debug.Print i: Next i
End Sub

Dana

"Tushar Mehta" wrote in message
news:MPG.1b82ad57e64d231d9898b9@news-server...
Tom's comment about precision loss in cells also applies to VBA. Try
this simple test. In the Immediate Window type in

for i=2.00 to 3.00 step 0.01:debug.Print i:next i

Upto 2.23 everything is as expected. Then, instead of 2.24, I get
2.23999999999999. By contrast, the following gave accurate results
from 2 to 3:

For xStep=0 to (3-2)/0.01:x=2+xStep*0.01:debug.Print x:next xstep

So, you may want to try:

dim xStep as integer,x as double
'might need long rather than integer
For xStep=0 to (gb-ga)/0.01
x=ga+xStep*0.01
...
next xStep

Also, when you share code, it would help if you included the variable
declaration statements.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , Peer
says...
I have tried every datatype it could possibly be;

integer, long, single, double, even variant after which I defined
decimal numbers with cdec.

It was the first solution (or error) I thought of.

And the numbers are exactly the numbers I'm searching for, beause there
are no formula's in the range, and the values are manual input.

:confused:


---
Message posted from http://www.ExcelForum.com/