That would be correct.

--
Regards,
Tom Ogilvy

"tim" wrote in message
...
Tom,

Thanks for the alternative. Just to make sure that I understand the
following line:
if not MyRange1 is Nothing then
This statement is checking to see if there are any blank cells, correct?
IOW, if there is 'nothing' in MyRange1, then there are no blank cells that
need attention.

Regards,
Tim

"Tom Ogilvy" wrote:

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