Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying down a formula only where there is data
Hello:
I created a macro that calculates, within a column, percentages based on data in the columns to the immediate left. The only issue with this macro is that the column is only copied "down" 604 rows in the spreadsheet. I can modify the macro in VBA to copy straight down to the last row possible in Excel. But, I would prefer to have it copied down to the last row that contains data to the immediate left of the column. (This column is column L and is the last column in my spreadsheet.) Here is the code that does this copying: Selection.AutoFill Destination:=Range("L2:L604") Range("L2:L604").Select How can I (a) modify the code to just copy down to that last row of data and (b) save this modfication in VBA so that I can re-use the .xlt file? Thanks! childofthe1980s |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying down a formula only where there is data
Selection.AutoFill Destination:=Range(range("L2"), cells(rows.count,
"L").end(xlup)) Range(range("L2"), cells(rows.count, "L").end(xlup)).select -- HTH... Jim Thomlinson "childofthe1980s" wrote: Hello: I created a macro that calculates, within a column, percentages based on data in the columns to the immediate left. The only issue with this macro is that the column is only copied "down" 604 rows in the spreadsheet. I can modify the macro in VBA to copy straight down to the last row possible in Excel. But, I would prefer to have it copied down to the last row that contains data to the immediate left of the column. (This column is column L and is the last column in my spreadsheet.) Here is the code that does this copying: Selection.AutoFill Destination:=Range("L2:L604") Range("L2:L604").Select How can I (a) modify the code to just copy down to that last row of data and (b) save this modfication in VBA so that I can re-use the .xlt file? Thanks! childofthe1980s |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying down a formula only where there is data
Hmm....I got a debugger error on that first line when I used that
programming...please help. Here's the full program including those two lines that you suggested: ActiveWindow.LargeScroll ToRight:=1 ActiveWindow.SmallScroll ToRight:=-5 Range("L2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)" Range("L2").Select Selection.AutoFill Destination:=Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)) Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)).Select Columns("L:L").Select Selection.NumberFormat = "0%" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range(Range("L2"), Cells(Rows.Count, "L").End(xlUp)).Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "% Below Min" Range("L3").Select Columns("L:L").EntireColumn.AutoFit ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select End Sub "Jim Thomlinson" wrote: Selection.AutoFill Destination:=Range(range("L2"), cells(rows.count, "L").end(xlup)) Range(range("L2"), cells(rows.count, "L").end(xlup)).select -- HTH... Jim Thomlinson "childofthe1980s" wrote: Hello: I created a macro that calculates, within a column, percentages based on data in the columns to the immediate left. The only issue with this macro is that the column is only copied "down" 604 rows in the spreadsheet. I can modify the macro in VBA to copy straight down to the last row possible in Excel. But, I would prefer to have it copied down to the last row that contains data to the immediate left of the column. (This column is column L and is the last column in my spreadsheet.) Here is the code that does this copying: Selection.AutoFill Destination:=Range("L2:L604") Range("L2:L604").Select How can I (a) modify the code to just copy down to that last row of data and (b) save this modfication in VBA so that I can re-use the .xlt file? Thanks! childofthe1980s |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying down a formula only where there is data
Actually, here is the solution:
ActiveWindow.LargeScroll ToRight:=1 ActiveWindow.SmallScroll ToRight:=-5 Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).FormulaR1C1 = "=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)" Columns("L:L").NumberFormat = "0%" Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value = Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value Range("L1").Value = "% Below Min" Application.CutCopyMode = False Columns("L:L").EntireColumn.AutoFit ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select "Jim Thomlinson" wrote: Selection.AutoFill Destination:=Range(range("L2"), cells(rows.count, "L").end(xlup)) Range(range("L2"), cells(rows.count, "L").end(xlup)).select -- HTH... Jim Thomlinson "childofthe1980s" wrote: Hello: I created a macro that calculates, within a column, percentages based on data in the columns to the immediate left. The only issue with this macro is that the column is only copied "down" 604 rows in the spreadsheet. I can modify the macro in VBA to copy straight down to the last row possible in Excel. But, I would prefer to have it copied down to the last row that contains data to the immediate left of the column. (This column is column L and is the last column in my spreadsheet.) Here is the code that does this copying: Selection.AutoFill Destination:=Range("L2:L604") Range("L2:L604").Select How can I (a) modify the code to just copy down to that last row of data and (b) save this modfication in VBA so that I can re-use the .xlt file? Thanks! childofthe1980s |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying down the formula just for data | Excel Programming | |||
copying down the formula just for data | Excel Programming | |||
Copying Data/Formula | Excel Worksheet Functions | |||
formula copying data and lay out | Excel Discussion (Misc queries) | |||
Copying cell data without the formula | Excel Programming |