using decimal numbers in for-next statement

[Peer]

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

 

[Harald Staff]

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

 

[Tom Ogilvy]

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

 

[Peer]

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

 

[Harald Staff]

Hi

Which datatypes are ga and gb declared as ? I'd think the problems lie here:
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

 

[Tom Ogilvy]

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.

 

[Peer]

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

 

[Tom Ogilvy]

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.

 

[Dana DeLouis]

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

 

[Tushar Mehta]

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

 

[Peer]

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
And the solution provided Tushar works perfectly!

Again tnx a lot

 

[Dana DeLouis]

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