Select next Cell Down

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RyGuy - 27 Sep 2007 02:21 GMT

I am trying to figure out how to select the current cell, and copy the
formula down one row. I am playing with:

For i = 3 To iLastRow + 1
If ActiveCell.Offset(-1, 0).Value <> ActiveCell.Offset(-1, -1).Value Then
ActiveCell.Offset(1, 0).Select
Selection.AutoFill Destination:=Range("C2:C" & Cell.Row + 1),
Type:=xlFillDefault
iStart = i
End If
Next i

Something seems to be failing here, and I can't tell what it is. Can anyone
tell me?

Thanks,
Ryan--

Reply to this Message

Joel - 27 Sep 2007 03:11 GMT

I prefer to do it this way.

for i = 2 to LastRow
if cells(i,"C").value <> cells(i + 1,"C").value then
cells(i + 1).formula = cells(i,"C")
end if
next i

> I am trying to figure out how to select the current cell, and copy the
> formula down one row. I am playing with:
[quoted text clipped - 13 lines]
> Thanks,
> Ryan--

Reply to this Message

RyGuy - 27 Sep 2007 06:37 GMT

Thanks for the look Joel. The code looks like it will work, but for some
reason it doesn't. It fails on this line:
Selection.AutoFill Destination:=Range("C2:C" & Cell.Row + 1),
Type:=xlFillDefault

All of my code is pasted below:
Sub Final2()
Dim iStart As Long
Dim sFormula As String
Dim iLastRow As Long
Dim i As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
sFormula = "=IF(ROWS(R<row>C:RC)<=R1C[-1],""A""&" & _
"SMALL(IF(ISNUMBER(SEARCH(RC[-2]," & _
"'Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROW('Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROWS(R<row>C:RC)),"""")"

iStart = 2
For i = 3 To iLastRow + 1
If ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, -1).Value Then
.Cells(i - 1, "C").FormulaArray = Replace(sFormula, "<row>", iStart)
iStart = i
End If

If ActiveCell.Offset(-1, 0).Value <> ActiveCell.Offset(-1, -1).Value Then
ActiveCell.Offset(1, 0).Select
Selection.AutoFill Destination:=Range("C2:C" & Cell.Row + 1),
Type:=xlFillDefault
iStart = i
End If

Next i
End With
End Sub

Can you tell what the problem is? I can't see it.

Thanks,
Ryan---

> I prefer to do it this way.
>
[quoted text clipped - 21 lines]
> > Thanks,
> > Ryan--

Reply to this Message

Joel - 27 Sep 2007 10:15 GMT

The test macro below works. Your problem is cell (without an s) is not
defined.

Selection.AutoFill Destination:=Range("C2:C" & Cell.Row + 1),

Maybe it shoud be activecell + 1.

Sub test()
Range("C2").Select
Set cell = Range("C20")
Set fillrange = Range(ActiveCell, _
ActiveCell.Offset(1, 0))
fillrange.AutoFill _
Destination:=Range("C2:C" & cell.Row + 1), _
Type:=xlFillDefault
End Sub

> Thanks for the look Joel. The code looks like it will work, but for some
> reason it doesn't. It fails on this line:
[quoted text clipped - 65 lines]
> > > Thanks,
> > > Ryan--

Reply to this Message