For Each cell in Range
 

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

For Each cell in Range
 

I have not been able to use this structure myself if the Named Range is
dynamic. It seems that it only works for fixed ranges.

"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

For Each cell in Range
 

Tim,

Your formula was incorrect, and you had the wrong variable in the Next
clause.

This worked fine for me, with a dynamic range

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 myCell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tim" wrote in message
...
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

For Each cell in Range
 

Dynamic range should work fine in this usage as long as it defines a
legitimate range.

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
I have not been able to use this structure myself if the Named Range is
dynamic. It seems that it only works for fixed ranges.

"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

For Each cell in Range
 

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

For Each cell in Range
 

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

For Each cell in Range
 

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

For Each cell in Range
 

"tim" wrote in message
...
Howdy,


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


just, as idea, why not use "selection" instead of named range?
it is a very quick method of passing area to macro.
just select cells you are intertested on worksheet,
alt+f8, select macro name, and done!

sub test1
Dim c As Variant
For Each c In Selection.Cells
MsgBox CStr(c.Value)
Next
end sub

you don't need "next [varname]", since interpreter use right variable
next only is sufficiant.

For Each cell in Range
 

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

For Each cell in Range
 

Sali,

Thanks for the comments...

The reason I elected not to do a "selection" is the range is dynamic,
depending on other inputs. I could select the range, but that seems to be
redundant. My impression is, if you can do something without "selecting" you
are better off (again my impression). The reason I use the [varname] with
"next" is for sanity, as the loops become nested (beyond this example) it
helps me to know which "next" goes where...

Regards,
Tim

"sali" wrote:

"tim" wrote in message
...
Howdy,


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


just, as idea, why not use "selection" instead of named range?
it is a very quick method of passing area to macro.
just select cells you are intertested on worksheet,
alt+f8, select macro name, and done!

sub test1
Dim c As Variant
For Each c In Selection.Cells
MsgBox CStr(c.Value)
Next
end sub

you don't need "next [varname]", since interpreter use right variable
next only is sufficiant.

For Each cell in Range
 

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