Dim r As Long, c As Long, X As Long
For r =1 To 10
'For c = 1 to 2
10 X = WorksheetFunction.RandBetween(1, 10)
If WorksheetFunction.CountIf(Range("A1:A10"), X) >= 1 Then GoTo 10 'find another number
Cells(r, 1) = X
I don't think this can be done with formulas in the cell without iterative calculations turned on. If you are willing to use iterative calculations then you can take a look at http://www.mrexcel.com/forum/e…-non-matching-number.html and play around with the formula posted by Marcelo Branco.