Announcement

Collapse
No announcement yet.

Add Relative/Absolute Formula To Range Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

    Code:
            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..)
    Asus P6T7 / i7-980x @4.1ghz / EAH5970 / 12gb Dominator / 2x Reodrive 480's
    Koolance liquidcooled CPU,GPU,NB,SB,RAM,MB,PSU, TMS-200 controlled

  • #2
    Re: Relative And Absolute Within Same Formula

    Can you use Dynamic Named Ranges?

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

    Comment


    • #3
      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

      Code:
              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:

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

      Comment


      • #4
        Re: Relative And Absolute Within Same Formula

        use code LIKE;

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

        Comment

        Working...
        X