Results 1 to 8 of 8

Thread: Insert Excel Formula With Relative Reference From VB

Threaded View

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Insert Excel Formula With Relative Reference From VB

    Is there a "Best"l way to handle Excel Formula insertion from VB.

    For example:

    Interestingly the first formula worked BUT the second formula failed with "Application defined or Object defined error". The formula is the same except for a subtraction (- I7) at end of each condition.

    'Worked
    .Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'GROSS

    'Failed
    .Cells(row, "J").Formula = "=IF(B7=""B""), ((F7-D7) * C7 * 50) - I7, ((F7-D7) * C7 * 50) - I7)" 'NET

    ========================
    QUESTIONS

    If a formula contains a relative reference. and is inserted into each Excel row from VB using a For/Next or Do/Loop will Excel automatically adjust the Formula??

    What's the best way to insert a Formula into Excel for VB:
    1) As above using Formula Property

    2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel sheet to another (NOTE: this would not allow creating Excel a new Workbook directly form VB)

    3) First Create Names and then Create Formula using those Names prior to using the Formula property??

    4) Other

    =======================================
    Last edited by dw85745; Jul 20th, 2008 at 10:19 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width