# divisor in cell

Status
Not open for further replies.

#### steve0075

##### New Member
Suppose the number 50 is in A1. I need a formula in B1 that will generate a random number that is a divisor of 50 (i.e. 1,2,5,10,25, or 50). I need this to work with any number (not just 50). Any help would be greatly appreciated.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### hedrijw

##### Board Regular
Instead of a formula, you can do this in VBA on the applicable worksheets code, i.e:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'-----------------------------------------------------------------------------------------
dim temp as double
If Target.Address<> "\$A\$1" Then Exit Sub ' Only handle changes to A1
temp = Target ' Get value entered in A1
Randomize ' Get random seed
Do
divisor = Int((temp * Rnd) + 1) ' Generate random divisor
Loop Until temp Mod divisor = 0 ' Loop until temp is evenly divisible
[b1] = divisor ' Show valid divisor
'-----------------------------------------------------------------------------------------
End Sub

Right click on the worksheet's tab and click on View Code

Find the Private Sub Worksheet_Change(ByVal Target As Excel.Range) statement and insert everything between the dashed lines after it.

Every time a number is entered in A1, a valid divisor will appear in B1
This message was edited by hedrijw on 2002-08-23 09:53

#### Juan Pablo González

##### MrExcel MVP
Status
Not open for further replies.

Replies
20
Views
597
Replies
4
Views
200
Replies
7
Views
498
Replies
9
Views
334
Replies
6
Views
224

1,191,163
Messages
5,985,028
Members
439,934
Latest member
Verdiana

### 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