Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
21st September 2007
Posts
26

## Add Relative/Absolute Formula To Range Macro

quick question, using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code

VB:
```Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial

```
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9?
and how can i select the two cells? (again, cannot be absolute..)

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Relative And Absolute Within Same Formula

Can you use Dynamic Named Ranges?

http://www.ozgrid.com/Excel/DynamicRanges.htm

Excel Video Tutorials / Excel Dashboards Reports

3. Banned
Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Relative And Absolute Within Same Formula

Originally Posted by nastynate
quick question, using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code

VB:
```Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial

```
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9?
and how can i select the two cells? (again, cannot be absolute..)
Trying to mix R1C1 and A1 style notation is never going to work. Just stick with R1C1:

VB:
```lRow = ActiveCell.Row
'...
Range("I" & lRow).FormulaR1C1 = "=Sum(R8C:R[-1]C)"
Range("J" & lRow).FormulaR1C1 = "=Sum(R8C:R[-1]C)"
'etc etc

```
Richard

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Relative And Absolute Within Same Formula

use code LIKE;

VB:
```Range("H1",Cells(Rows.Count,"H").End(XlUp)).Offset(0,2).FormulaR1C1 = "=Sum(R8C:R[-1]C)"

```

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno