Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Insert Relative Formula Into Range Via VBA

  1. #1
    Join Date
    16th March 2006
    Posts
    65

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

    Re: Cell Formulas With Forms

    Hi Chaz

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Cell Formulas With Forms

    You want to use formulas to do this?

    VB:
    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.
    MS MVP - Excel

  4. #4
    Join Date
    16th March 2006
    Posts
    65

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Macro Code To Insert Relative Formula Into Range
    By Andy3L in forum EXCEL HELP
    Replies: 5
    Last Post: June 17th, 2008, 12:15
  2. Replies: 8
    Last Post: April 30th, 2008, 01:15
  3. Relative Indirect Formula to SUM range
    By mcgoony in forum EXCEL HELP
    Replies: 22
    Last Post: February 14th, 2008, 03:27
  4. Fill Relative Formula Into Range
    By Chris K in forum EXCEL HELP
    Replies: 11
    Last Post: January 23rd, 2008, 10:25
  5. Add Relative/Absolute Formula To Range Macro
    By nastynate in forum EXCEL HELP
    Replies: 3
    Last Post: September 22nd, 2007, 12:36

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