Sub Macro1()
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Select
Selection.FormulaArray = "=SMALL(IF(RC[8]:RC[27]<>"""",RC[8]:RC[27],9^9),RANDBETWEEN(1,COUNTA(RC[8]:RC[27])))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next
End Sub