Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select next Cell Down
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-- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select next Cell Down
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 "RyGuy" wrote: 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-- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select next Cell Down
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<rowC: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<rowC: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--- "Joel" wrote: 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 "RyGuy" wrote: 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-- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select next Cell Down
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 "RyGuy" wrote: 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<rowC: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<rowC: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--- "Joel" wrote: 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 "RyGuy" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
How to point to (select) a cell to the left from a cell where I enter the = equal sign? | Excel Discussion (Misc queries) | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) |