Insert Excel Formula With Relative Reference From VB-VBForums
Results 1 to 8 of 8

Thread: Insert Excel Formula With Relative Reference From VB

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    1,973

    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

    Re: Insert Excel Formula With Relative Reference From VB

    You have at least one typo in the second formula - check the brackets.


    My choice would be option 1.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    1,973

    Re: Insert Excel Formula With Relative Reference From VB

    Thanks si_the_geek -- Missed that one big time -- OLD EYES.
    Formula now is inserted properly.

    ------------------------

    Any feedback on my other questions?

    The answer to this one:

    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??
    is NO.

    Calculate is off when entering formulas, but even after turning back on OR
    manually calculating EXCEL sheet afterwords, Formulas remain as entered Someway(?) Excel needs to be told cell references in formula are relative
    after entering?
    Last edited by dw85745; Jul 20th, 2008 at 11:16 AM.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    1,973

    Re: Insert Excel Formula With Relative Reference From VB

    OK. Looks like I have a solution.

    For whatever reason Excel does NOT adjust an inserted general relative reference formula (the first same cell formula which is copied into multiple cells) to their relative cell references.

    I'm leaving this thread open if some knows differently.

    ========================

    SOLUTION

    The solution I came up with is to build a dynamic formula (a string that reflects the correct row/column within the formula with each loop pass). This dynamic string is then assigned to the Excel Formula Property and inserted into Excel from VB at the correct Cell Formula location.

  5. #5
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Insert Excel Formula With Relative Reference From VB

    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??
    Depends on how you insert it....

    If you do a "Paste Special Formulas" then the formulas in the respective cell will get updated for example...

    Code:
    Sub Test()
    
    Row = 1
    Cells(Row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))"
    
    'Copy the first formula
    Range("H1").Copy
    
    'Paste Special Formulas
    'Change Range as applicable
    Range("H2:H18").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    
    End Sub
    Hope this is what you wanted?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  6. #6
    Junior Member Colin_L's Avatar
    Join Date
    Mar 2008
    Location
    London, UK
    Posts
    21

    Re: Insert Excel Formula With Relative Reference From VB

    The quickest and cleanest way to do this is to directly and entirely utilise the range object's formula property. This single line of code gives the same result as the code in #5 - Excel increments the row numbers for you....

    Code:
    Range("H1:H18").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))"
    • No loops required.
    • No copy and paste.

    HTH,

    Colin
    Last edited by Colin_L; Jul 21st, 2008 at 07:14 AM.

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    1,973

    Re: Insert Excel Formula With Relative Reference From VB

    Thanks for responses. Where were you Saturday :>)

    Appears to be a simpler solution as only the Range need to be adjusted dynamically rather than each row reference within the formula.

    Will give it a test

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Insert Excel Formula With Relative Reference From VB

    Quote Originally Posted by dw85745
    Thanks for responses. Where were you Saturday :>)

    Appears to be a simpler solution as only the Range need to be adjusted dynamically rather than each row reference within the formula.

    Will give it a test
    Coding is never the problem... it is always the logic that we need to ZERO on. Once the logic is clear and makes sense, it is easy to code And once the code is created you can always work on it to make it better and better...
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.