For Each cell in Range

[Guest]

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

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[sali]

Howdy,

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.

 

[Guest]

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

 

[Guest]

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

Howdy,

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.

 

[Tom Ogilvy]

That would be correct.

--
Regards,
Tom Ogilvy

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