Distribute randomly or by percentage in the range.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Using Excel 2010
Hello,

I have 16 set in the range F6:F21. Which I want to distribute randomly or by percentage within range D6:D90. Please suggest any VBA or formula.

Note: Distribution range can be varying from this example 85 to any may be 2500.

Excel Questions.xlsm
ABCDEFG
1
2
3
4Distribute
585Rand Or %16
6100
7201
8302
940M
10510
11611
12712
1381M
14920
151021
161122
17122M
1813M0
1914M1
2015M2
2116MM
2217
2318
2419
2520
2621
2722
2823
2924
3025
3126
3227
3328
3429
3530
3631
3732
3833
3934
4035
4136
4237
4338
4439
4540
4641
4742
4843
4944
5045
5146
5247
5348
5449
5550
5651
5752
5853
5954
6055
6156
6257
6358
6459
6560
6661
6762
6863
6964
7065
7166
7267
7368
7469
7570
7671
7772
7873
7974
8075
8176
8277
8378
8479
8580
8681
8782
8883
8984
9085
91
92
Hoja1


Regards,
Moti
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hello, searching in the excel forums I found under this thread similar solution
Random Letters
Which I was able to modified as per my need as below…Please also I want can someone check is it correct modified?
VBA Code:
Sub DistributeRandomLettersNoRepeatsInRows()
  Dim X As Long, RandomIndex As Long, row As Range, TempElement As String, Arr() As String
 
    Range("D6:D2500").ClearContents
    
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).row
  
    Arr = Split("00 01 02 0M 10 11 12 1M 20 21 22 2M M0 M1 M2 MM")  ' Single space delimited list of letters
    For Each row In Range("D6:D" & lngLastRow).Rows
    For X = UBound(Arr) To 0 Step -1
      RandomIndex = Int((X - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(X)
      Arr(X) = TempElement
    Next
    row = (Arr)
  Next
End Sub
In the above macro, the 16 combinations array is built-in inside the macro I do not know how it can be selected via input box from cell range F6:F21.

Please help to make it possible via input box selection.

Regards,
Moti
 
Upvote 0
It seems that it is not possible to pick 16 option from sheet cell selection via input any other idea please suggest
 
Upvote 0
Please not I have open query also under this thread.


Regards,
Moti
 
Upvote 0
Hello, I found one one more option in this require to fill InputBox desire Patt with space...but still i need InputBox which give an option pattern could be selected from the sheet. is it possible?

'VBA code help! Removing unwanted columns using inputbox
'VBA code help! Removing unwanted columns using inputbox

VBA Code:
Sub DistributeRandomLettersNoRepeatsInRows_1()
  Dim x As Long, RandomIndex As Long, row As Range, TempElement As String, Arr() As String, ibox As String
 
    Range("D6:D2500").ClearContents
    
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).row
  
    'Arr = Split("00 01 02 0M 10 11 12 1M 20 21 22 2M M0 M1 M2 MM")  ' Single space delimited list of letters
    
    ibox = InputBox("Enter Partido P15 separated by Single Space")
    
    If Len(ibox) = 0 Then
    Exit Sub
    Else
    Arr = Split(ibox, " ")
    
    For Each row In Range("D6:D" & lngLastRow).Rows
    For x = UBound(Arr) To 0 Step -1
      RandomIndex = Int((x - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(x)
      Arr(x) = TempElement
    Next
    row = (Arr)
  Next
 End If

End Sub

Regards,
Moti
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top