Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Add Relative/Absolute Formula To Range Macro

  1. #1
    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. #2
    Join Date
    19th January 2005
    Location
    Michigan, USA
    Posts
    844

    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. #3
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Relative And Absolute Within Same Formula

    Quote 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. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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)" 
    
    

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: December 17th, 2011, 03:12
  2. Macro Code To Insert Relative Formula Into Range
    By Andy3L in forum EXCEL HELP
    Replies: 5
    Last Post: June 17th, 2008, 12:15
  3. Relative/absolute Macro Recording
    By jholmstrom in forum EXCEL HELP
    Replies: 3
    Last Post: May 12th, 2007, 00:15
  4. Absolute macro part relative
    By icepuck in forum EXCEL HELP
    Replies: 4
    Last Post: July 15th, 2005, 07:36
  5. Convert Formula References. Relative/Absolute
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: June 30th, 2005, 13:35

Bookmarks

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