+ Reply to Thread
Results 1 to 3 of 3

Create Formulas Using VBA So that the Formula auto computes

  1. #1
    Registered User
    Join Date
    01-20-2012
    Location
    NYC, New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    Create Formulas Using VBA So that the Formula auto computes

    I want to create a formula in excel and I am using the below code. It works however, if there is no data in the referenced cells so nothing gets computed until data is entered and the macro is run again. I want the macro to run in the begining and input the formulas in the designated cells so when the user updates the referenced cells it auto computes.

    Also, is there a better way to sum up the second portion of the code? As of now I am rerfencing each cell indicidually.


    If Cells(i, 8).Formula = "GROSS PROFIT. SUBTRACT LINE 2 FROM LINE 1." Then
    Cells(i, 8).Font.Bold = True
    j = 9
    Do While Cells(1, j) <> ""
    Cells(i, j).Formula = Cells(i - 2, j).Formula - Cells(i - 1, j)
    j = j + 1

    Loop


    ElseIf Cells(i, 8).Formula = "TOTAL INCOME. ADD LINES 3 THROUGH 10." Then
    Cells(i, 8).Font.Bold = True
    j = 9
    Do While Cells(1, j) <> ""
    Cells(i, j).Formula = Cells(i - 7, j).Formula + Cells(i - 6, j) + Cells(i - 5, j) + Cells(i - 4, j) + Cells(i - 3, j) + Cells(i - 2, j) + Cells(i - 1, j)
    j = j + 1

    Loop

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Create Formulas Using VBA So that the Formula auto computes

    Why do you need VBA for this? A properly structured regular formula can probably do what you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-20-2012
    Location
    NYC, New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Create Formulas Using VBA So that the Formula auto computes

    So the spreadsheet is created in SQL and/or Alteryx and produces many tabs. Since there will be many tabs and the spreadsheet may not be consistent I wanted a Macro to create the formulas for other users who are not Excel savvy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. create custom auto fill formulas in Excel 2010
    By wk3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2016, 09:25 AM
  2. Formula computes with the value of zero.
    By dmerrithew in forum Excel General
    Replies: 3
    Last Post: 11-18-2014, 11:09 PM
  3. Replies: 0
    Last Post: 08-14-2013, 10:34 AM
  4. Formula computes correctly, displays wrongly
    By Joseph Giri in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-22-2013, 11:11 AM
  5. Replies: 7
    Last Post: 11-30-2012, 07:37 PM
  6. [SOLVED] Does anyone have a weekly schedule that computes total hours work
    By Guillermo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-03-2006, 01:10 AM
  7. [SOLVED] How can formulas be used to create another formula?
    By Geag in forum Excel General
    Replies: 4
    Last Post: 07-29-2005, 05:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1