# Finding the factors of a number

#### mortgageman

##### Well-known Member
I am working on a spreadsheet for my 6th grader. Does anyone out there have a UDF that generates all the factors of a number? (Yeah I know I could write it, but I hate to reinvent the wheel. If it is out there, I would appreciate it)

Gene, "The Mortgage Man", Klein

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### dantb

##### Active Member
This Came from Microsoft and seems to work. Dan

Sub GetFactors()
Dim Count As Integer
Dim NumToFactor As Single 'Integer limits to < 32768
Dim Factor As Single
Dim y As Single
Dim IntCheck As Single

Count = 0
Do
NumToFactor = _
Application.InputBox(Prompt:="Type integer", Type:=1)
'Force entry of integers greater than 0.
IntCheck = NumToFactor - Int(NumToFactor)
If NumToFactor = 0 Then
Exit Sub
'Cancel is 0 -- allow Cancel.
ElseIf NumToFactor < 1 Then
MsgBox "Please enter an integer greater than zero."
ElseIf IntCheck > 0 Then
MsgBox "Please enter an integer -- no decimals."
End If
'Loop until entry of integer greater than 0.
Loop While NumToFactor <= 0 Or IntCheck > 0
For y = 1 To NumToFactor
'Put message in status bar indicating the integer being checked.
Application.StatusBar = "Checking " & y
Factor = NumToFactor Mod y
'Determine if the result of division with Mod is without _
remainder and thus a "factor".
If Factor = 0 Then
'Enter the factor into a column starting with the active cell.
ActiveCell.Offset(Count, 0).Value = y
'Increase the amount to offset for next value.
Count = Count + 1
End If
Next
'Restore Status Bar.
End Sub

Sub GetPrime()
Dim Count As Integer
Dim BegNum As Single 'Integer limits to < 32768
Dim EndNum As Single
Dim Prime As Single
Dim flag As Integer
Dim IntCheck As Single
Count = 0

Do
BegNum = _
Application.InputBox(Prompt:="Type beginning number.", Type:=1)
'Force entry of integers greater than 0.
IntCheck = BegNum - Int(BegNum)
If BegNum = 0 Then
Exit Sub
'Cancel is 0 -- allow Cancel.
ElseIf BegNum < 1 Then
MsgBox "Please enter an integer greater than zero."
ElseIf IntCheck > 0 Then
MsgBox "Please enter an integer -- no decimals."
End If
'Loop until entry of integer greater than 0.
Loop While BegNum <= 0 Or IntCheck > 0

Do
EndNum = _
Application.InputBox(Prompt:="Type ending number.", Type:=1)
'Force entry of integers greater than 0.
IntCheck = EndNum - Int(EndNum)
If EndNum = 0 Then
Exit Sub
'Cancel is 0 -- allow Cancel.
ElseIf EndNum < BegNum Then
MsgBox "Please enter an integer larger than " & BegNum
ElseIf EndNum < 1 Then
MsgBox "Please enter an integer greater than zero."
ElseIf IntCheck > 0 Then
MsgBox "Please enter an integer -- no decimals."
End If
'Loop until entry of integer greater than 0.
Loop While EndNum < BegNum Or EndNum <= 0 Or IntCheck > 0

For y = BegNum To EndNum
flag = 0
z = 1
Do Until flag = 1 Or z = y + 1
'Put message into Status Bar indicating the integer and _
divisor in each loop.
Application.StatusBar = y & " / " & z
Prime = y Mod z
If Prime = 0 And z <> y And z <> 1 Then
flag = 1
End If
z = z + 1
Loop

If flag = 0 Then
'Enter the factor into a column starting with the active cell.
ActiveCell.Offset(Count, 0).Value = y
'Increase the amount to offset for next value.
Count = Count + 1
End If
Next y
'Restore Status Bar.
End Sub Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,003
Messages
5,834,828
Members
430,324
Latest member
bosphoruskid ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back