Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofill script question
Range("L6").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")" Range("L6").Select Selection.AutoFill Destination:=Range("L6:L483") Range("L6:L483").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""Unreviewed""" I've got this formula...what i need to know is how do I get the script to autofill to the last cell??As it will be different for each sheet, as not all range will end up at L483, i have some that will end at L700, L658. So how do i make the script always go to the last cell?? Please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofill script question
hi,
there is more that one way to do this. here's one Sub copytest() Range("L6").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")" ActiveCell.Copy Range(Range("L6"), Range("L6").Offset(0, -1) _ .End(xlDown).Offset(0, 1)).Select ActiveSheet.Paste 'rest of your code here? End Sub this assumes that the K column is a solid block of data. if not then come up from the bottom. Sub copytest() Range("L6").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")" ActiveCell.Copy Range("k10000").End(xlUp).Offset(0, 1).Select Range(ActiveCell, ActiveCell.End(xlUp)).Select ActiveSheet.Paste 'rest of your code here? End Sub and there are other ways too. this is just 2 Regards FSt1 "Gor_yee" wrote: Range("L6").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")" Range("L6").Select Selection.AutoFill Destination:=Range("L6:L483") Range("L6:L483").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""Unreviewed""" I've got this formula...what i need to know is how do I get the script to autofill to the last cell??As it will be different for each sheet, as not all range will end up at L483, i have some that will end at L700, L658. So how do i make the script always go to the last cell?? Please help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofill script question
Example code.........
Do you have a column to check for last cell? I used adjacent column K in the example. Sub Auto_Fill() Dim Lrow As Long With ActiveSheet Range("L6").Formula = "=B1+C1" Lrow = Range("K" & Rows.Count).End(xlUp).Row Range("L6:L" & Lrow).FillDown End With End Sub Gord Dibben MS Excel MVP On Sat, 04 Aug 2007 10:27:35 -0700, Gor_yee wrote: Range("L6").Select ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")" Range("L6").Select Selection.AutoFill Destination:=Range("L6:L483") Range("L6:L483").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""Unreviewed""" I've got this formula...what i need to know is how do I get the script to autofill to the last cell??As it will be different for each sheet, as not all range will end up at L483, i have some that will end at L700, L658. So how do i make the script always go to the last cell?? Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about Autofill with a formula | Excel Discussion (Misc queries) | |||
question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
autofill question | Excel Discussion (Misc queries) | |||
Autofill question | Excel Worksheet Functions |