simple little loop

[ryguy7272]

I'm stuck on a simple little loop late on a Friday night:

Dim redRng As Range
Dim longrow As Long
longrow = Cells(Rows.Count, "I").End(xlUp).Row

Set redRng = Range("I4", Range("I" & longrow).End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]*RC[-7]"
ActiveCell.Offset(1, -1).Select
End If

Next cell

My loop seems to run four times and then quits. I guess it comes from the
I4, but I'm not sure why it's doing that. Is there a better, clearer, way to
do this?

Thanks,
Ryan--

 

[JLGWhiz]

Why not:

If cell.Value <> "" Then
cell.Offset(0, 1).FormulaR1C1 = "=RC[-6]*RC[-7]"
End If

The ActiveCell might not be the same as cell. You could be putting formulas
in places that you did not intend to put them. With the above code, you do
not need to use the second offset to return the focus to column I because it
never leaves it and the For next will take care of moving to the next row.

 

[ryguy7272]

I F8-through the code; I think it was just this one line:
Set redRng = Range("I4", Range("I4" & longrow).End(xlUp))

Seems to work now.

 

[Shane Devenshire]

Hi,

I generally use something like:

Range("I4:I" & [I65536].End(XLUp).Row).SpecialCells(XLCellTypeBlanks) =
yourFormula

No need to loop at all. Every formula is entered at once. No need to test
each cell one at a time.

A few points: when you use For Each you don't want to move the cursor, so
ActiveCell.Offset(0,1) is not a good idea.

The previous comment aside, ActiveCell.Offset(1, -1).Select moves the cursor
down one row and one column to the left. You are moving on the diagonal, is
that really what you want?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I F8-through the code; I think it was just this one line:
Set redRng = Range("I4", Range("I4" & longrow).End(xlUp))

Seems to work now.


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I'm stuck on a simple little loop late on a Friday night:

Dim redRng As Range
Dim longrow As Long
longrow = Cells(Rows.Count, "I").End(xlUp).Row

Set redRng = Range("I4", Range("I" & longrow).End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-6]*RC[-7]"
ActiveCell.Offset(1, -1).Select
End If

Next cell

My loop seems to run four times and then quits. I guess it comes from the
I4, but I'm not sure why it's doing that. Is there a better, clearer, way to
do this?

Thanks,
Ryan--