Forum Home
PC World Chat
 
Thread ID: 63034 2005-10-27 04:58:00 VBA question - I'm having a senior moment... Tony (4941) PC World Chat
Post ID Timestamp Content User
399673 2005-10-27 04:58:00 This has got to be sooo easy, but I can't get it.

I need an expression in VBA that for any n, will give me a rounded up integer that tells me how many times m will divide into it - bit like MOD in reverse.

eg:
if m=40
n=10, result=1
n=20, result=1
n=40, result=1

n=41, result=2
n=79, result=2
n=80, result=2

n=81, result=3

etc.

I'm trying to decide how many times I have to iterate something, depending on the value of n. M will be a constant.

I realize I could do it by looping round and subtracting and stuff like that, but there has got to be a more elegant way.

Hope this all makes sense! 		Tony (4941)
399674 2005-10-27 05:15:00 I believe there is a \ DIV operator that does integer division (not to be confused with / which gives you real numbers)

e.g

Dim MyValue
MyValue = 11 \ 4 ' Returns 2.

so you want:

result = n \ m + 1 		gibler (49)
399675 2005-10-27 06:54:00 Yeah, tried that, but as always, it is the boundary conditions that get ya .

Sub tstdiv()

Dim x
Dim y
Dim rcount
rcount = 40
For y = 1 To 100
x = y \ rcount + 1
Debug . Print y, x
Next y

End Sub
*******************
This produced this output:
*******************

1 1
2 1
3 1
4 1
. . . snip a lot . . .

38 1
39 1
40 2 ' should be 1
41 2
42 2
43 2
44 2

. . . snip some more . . .

77 2
78 2
79 2
80 3 ' should be 2
81 3
82 3
. . .


Hence my frustration! :badpc: 		Tony (4941)
399676 2005-10-27 07:40:00 If n Mod m = 0 Then
result = n \ m
Else
result = n \ m + 1
End If 		gibler (49)
399677 2005-10-27 07:49:00 Nice one Gibler, that did it. I don't like it, :) but it did it.

It still seems to me that you ought to be able to derive a simple expression that would do the trick, but maybe I'm just a hopeless dreamer, always striving for elegance and perfection...

Thanks for the help :thumbs: 		Tony (4941)
399678 2005-10-28 02:49:00 ummm.... would n \ (m+1) do it? Graham L (2)
399679 2005-10-28 03:27:00 ummm.... would n \ (m+1) do it?

80 DIV 41 = 1
81 DIV 41 = 1
82 DIV 41 = 2
83 DIV 41 = 2

Even if you add an extra one to all results you see that with n=81 it will be different from n=82, and according to the pattern they shouldn't be.

Then again it is a Friday afternoon ... 		gibler (49)
1