Select next Cell Down

[Guest]

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--

 

[Guest]

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

 

[Guest]

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---

 

[Guest]

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:
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.


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