Is there a way function in VBA that allows you to calcualte the
multiple of a number. That is, if the variable = 36, I want to
calculate all nine multiples:

M1=1 (1*36=36)
M2=2 (2*18=36)
M3=3 (3*12=36)
M4=4
M5=6
M6=9
M7=12
M8=18
M9=36

Thanks

TS

Terry

I haven't tested this by try looking at the MOD function (modulus)

If the MOD of a number divided by another number is ZERO then it divides
evenly (therefore its a mutliple) so I'm thinking a simple FOR/NEXT loop
counting from 1 up to the TEST number should provide you with all the
multiples.

Something like:

FOR X = 1 TO TESTNUM
IF MOD(TESTNUM,X)=0 then success
NEXT X

Greg

wrote in message
om...
Is there a way function in VBA that allows you to calcualte the
multiple of a number. That is, if the variable = 36, I want to
calculate all nine multiples:

M1=1 (1*36=36)
M2=2 (2*18=36)
M3=3 (3*12=36)
M4=4
M5=6
M6=9
M7=12
M8=18
M9=36

Thanks

TS

Terry,

Forgot to add that the function returns an array of values. To print on a
spreadsheet for example use something like

Dim i As Long
Dim aryValues()

aryValues = Divisors(36)

For i = LBound(aryValues) To UBound(aryValues)
Cells(i + 1, 1).Value = aryValues(i)
Next

--

HTH

Bob Phillips

"Bob Phillips" wrote in message
...
Terry,

Here's a simple brute-force solution

Function Divisors(num As Long)
Dim i As Long, j As Long
Dim aryNums()

If IsPrime(num) Then
ReDim aryNums(1)
aryNums(0) = 1
aryNums(1) = num
Divisors = aryNums()
Exit Function
End If

ReDim aryNums(0)
aryNums(0) = 1
j = 0
For i = 2 To (num \ 2)
If (num \ i) * i = num Then
j = j + 1
ReDim Preserve aryNums(j)
aryNums(j) = i
End If
Next
ReDim Preserve aryNums(j + 1)
aryNums(j + 1) = num
Divisors = aryNums()
End Function

Function IsPrime(num As Long) As Boolean
Dim i As Long
IsPrime = True
If num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If
End Function



--

HTH

Bob Phillips

wrote in message
om...
Is there a way function in VBA that allows you to calcualte the
multiple of a number. That is, if the variable = 36, I want to
calculate all nine multiples:

M1=1 (1*36=36)
M2=2 (2*18=36)
M3=3 (3*12=36)
M4=4
M5=6
M6=9
M7=12
M8=18
M9=36

Thanks

TS

You're welcome Greg, it's what it's for.

Bob


"Greg Glynn" wrote in message
...
Here's a simple way (Is stole Bob Philips' MOD test line .. Thanks Bob)

Sub ShowMults()
TestNum = 36
Results = ""
For x = 1 To TestNum
If TestNum Mod x = 0 Then
Foundcount = Foundcount + 1
If Foundcount 1 Then Results = Results & ", " & x Else: Results = x
End If
Next x
MsgBox ("Found " & Foundcount & " Multiples of " & TestNum & vbCrLf &
Results)
End Sub


wrote in message
om...
Is there a way function in VBA that allows you to calcualte the
multiple of a number. That is, if the variable = 36, I want to
calculate all nine multiples:

M1=1 (1*36=36)
M2=2 (2*18=36)
M3=3 (3*12=36)
M4=4
M5=6
M6=9
M7=12
M8=18
M9=36

Thanks

TS

Thanks. I used Gregs code originally because it was simpler for me to
understand. However I ran into another issue. I need to print the
multiple list in a sheet and I need each multiple in its own cell. I
know how to do this if the results are in a array. But the code in
Gregs example basically builds a moving text string with commas i.e.

1, 2, 3, 4, 6, 9, 12, 18, 36

How do I dump this text string into cells so that it look like this
(i.e. separate the numbers and remover the commas:

1
2
3
4
6
9
12
18
36

Thanks again.

TS

Terry,

I think that my earlier follow-up post does exactly what you want when
combined with my earlier code. You need to run the macro from a redundant
worksheet, that is not one with your main data on it.

--

HTH

Bob Phillips

wrote in message
om...
Thanks. I used Gregs code originally because it was simpler for me to
understand. However I ran into another issue. I need to print the
multiple list in a sheet and I need each multiple in its own cell. I
know how to do this if the results are in a array. But the code in
Gregs example basically builds a moving text string with commas i.e.

1, 2, 3, 4, 6, 9, 12, 18, 36

How do I dump this text string into cells so that it look like this
(i.e. separate the numbers and remover the commas:

1
2
3
4
6
9
12
18
36

Thanks again.

TS

If you multipled don't get to be too many:

Sub ShowMults()
Dim x As Long
Dim textNum As Long
TestNum = 10 ^ 5
Results = ""
For x = 1 To TestNum
If TestNum Mod x = 0 Then
Foundcount = Foundcount + 1
If Foundcount 1 Then Results = _
Results & ", " & x Else: Results = x
End If
Next x
varr = Evaluate("{" & Results & "}")
Cells(1, 1).Resize(UBound(varr) - _
LBound(varr) + 1, 1) = _
Application.Transpose(varr)

End Sub

--
Regards,
Tom Ogilvy

wrote in message
om...
Thanks. I used Gregs code originally because it was simpler for me to
understand. However I ran into another issue. I need to print the
multiple list in a sheet and I need each multiple in its own cell. I
know how to do this if the results are in a array. But the code in
Gregs example basically builds a moving text string with commas i.e.

1, 2, 3, 4, 6, 9, 12, 18, 36

How do I dump this text string into cells so that it look like this
(i.e. separate the numbers and remover the commas:

1
2
3
4
6
9
12
18
36

Thanks again.

TS