Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formula to the bottom of a range
I'm trying to create a macro that copies a formula in a column down to the
bottom of a range. For example, I have data in columns A - L. In column M, I want to create a formula and copy it down to the bottom of my data. The only problem is my data is dynamic. One day it might end on row 1038, another day it might end on row 1152. Anyone have VBA code that can help me do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formula to the bottom of a range
set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup) )
rng.offset(0,1).Formula = "=Sum(A1:C1)" make the formula relative to the first cell in your range and use absolute and relative addressing as appropriate. if you already have it in M1 for example, then it would be set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup) ) rng.offset(0,1).Formula = Range("M1").formula -- Regards, Tom Ogilvy "jeremy nickels" <jeremy wrote in message ... I'm trying to create a macro that copies a formula in a column down to the bottom of a range. For example, I have data in columns A - L. In column M, I want to create a formula and copy it down to the bottom of my data. The only problem is my data is dynamic. One day it might end on row 1038, another day it might end on row 1152. Anyone have VBA code that can help me do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formula to the bottom of a range
thanks Tom, that worked like a charm.
"Tom Ogilvy" wrote: set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup) ) rng.offset(0,1).Formula = "=Sum(A1:C1)" make the formula relative to the first cell in your range and use absolute and relative addressing as appropriate. if you already have it in M1 for example, then it would be set rng = range(cells(1,"L"),cells(rows.count,"L").End(xlup) ) rng.offset(0,1).Formula = Range("M1").formula -- Regards, Tom Ogilvy "jeremy nickels" <jeremy wrote in message ... I'm trying to create a macro that copies a formula in a column down to the bottom of a range. For example, I have data in columns A - L. In column M, I want to create a formula and copy it down to the bottom of my data. The only problem is my data is dynamic. One day it might end on row 1038, another day it might end on row 1152. Anyone have VBA code that can help me do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPYING OFFSET FORMULA WITHOUT THE DATA RANGE CHANGING-DESPERATE | Excel Worksheet Functions | |||
Copying a formula that contains a range of cell rows | Excel Discussion (Misc queries) | |||
Problem copying formula to range of cells | Setting up and Configuration of Excel | |||
Copying array formula to a range of cells... | Excel Programming | |||
How to: Add blank row at bottom of range | Excel Programming |