Results 1 to 8 of 8

Thread: Insert Excel Formula With Relative Reference From VB

  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.

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

    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
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    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
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  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
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    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
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

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