Announcement

Collapse
No announcement yet.

Insert Relative Formula Into Range Via VBA

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

  • Insert Relative Formula Into Range Via VBA

    Hi All
    I am updating a wsheet via a form using vb to locate next empy row etc.
    The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
    Previous entry to sheet:-
    A1=1
    B1=2
    c1=A1+B1(formula)

    New entry via form selecting next empty.
    A2=(updated from form.)
    B2=(Updated from form.)
    C2=(Update code VB)

    This is all okay and functional the problem starts from here
    I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.

    Thanks for any input
    Chaz
    ChazAlways strumming

  • #2
    Re: Cell Formulas With Forms

    Hi Chaz

    Use FormulaR1C1 property rather than Formula so the code you want then becomes:

    Code:
    Range("C2").FormulaR1C1 = "=RC[-1]+RC[-2]"
    This formula applied in any cell will always add the two cells to the immediate left of wherever you enter it.

    Richard

    Comment


    • #3
      Re: Cell Formulas With Forms

      You want to use formulas to do this?

      Code:
      Cells(1, "A") = 1
      
      Cells(2, "A").Resize(100).FormulaR1C1 = "=r[-1]c + 1"
      ' or
      Cells(2, "A").Resize(100).FormulaR1C1 = "=row() - row(r1c) + 1"
      Entia non sunt multiplicanda sine necessitate.

      Comment


      • #4
        Re: Insert Relative Formula Into Range Via VBA

        Thanks I just needed to know if it could be done. it may prove useful later in my project.
        ChazAlways strumming

        Comment

        Working...
        X