without looping:

sub AAA()
Dim myRange As Range
Dim myRange1 as Range

Set myRange = Range("defined_range")
On Error Resume Next
Set myRange1 = myRange.specialCells(xlBlanks)
On Error goto 0
if not MyRange1 is Nothing then
MyRange1.formula = "=Int(rand()*500+1)"
End if
With MyRange
.Formula = .Value
.Sort _
key1:=.Cells(1, 1) 'sort the range ascending
.Cells(1, 1).Select
End With
end Sub

--
Regards,
Tom Ogilvy


"tim" wrote in message
...
Howdy,

Thanks all for the replies. I thought it should work. I did make some
minir edits got it to work, albeit slowly. I have decided to populate a
varient array/range instead. Seems to be mucho faster....


Dim vArr As Variant
Dim i As Integer
Dim myRange As Range

Set myRange = Range("defined_range")

With myRange
ReDim vArr(1 To .Rows.Count, 1 To 1)
For i = 1 To UBound(vArr, 1)
vArr(i, 1) = myRange(i, 1) 'Get the existing value from the
range
If IsEmpty(vArr(i, 1)) Then 'If blank, then create random
number
for location
vArr(i, 1) = "=Int(rand()*500+1)"
End If
Next i
.Value = vArr
.Formula = .Value 'store the value versus the equation
.Sort _
key1:=.Cells(1, 1) 'sort the range ascending
.Cells(1, 1).Select

End With

Not sure it is the most optimized code, but seems to do the job...

Regards,
Tim

"gocush" wrote:

Tim
My quick response was untested. Apparently, the situation I am
referring to
is similar but not quite the same. I'll have to go back and check to
see
where the Dynamic range didn't work for me. sorry

"tim" wrote:

Howdy,

Seems so simple, but gives the "run time error 1004". "defined_Range"
is a
dynamic range in the workbook.


Dim myCell as Range

For Each myCell In Range("Defined_Range")
If IsEmpty(myCell) Then
myCell.Formula = "=Int(rand()*500+1"
myCell.Formula = myCell.Value
End If
Next rCell

Regards,
Tim