Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple little loop
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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple little loop
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" wrote in message ... 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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple little loop
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''. "ryguy7272" wrote: 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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple little loop
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 "ryguy7272" wrote: 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''. "ryguy7272" wrote: 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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Loop | Excel Programming | |||
Simple Loop Help Please | Excel Programming | |||
Simple Loop Help Please | Excel Programming | |||
Simple loop? | Excel Programming | |||
Some help w/ simple loop, please? | Excel Programming |