divisor in cell

[steve0075]

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.

 

[hedrijw]

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]

Duplicate: http://www.mrexcel.com/board/viewtopic.php?topic=19889&forum=2&3