
Jul 20th, 2008, 10:13 AM
#1
Thread Starter
PowerPoster
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"", ((F7D7) * C7 * 50), ((F7D7) * C7 * 50))" 'GROSS
'Failed
.Cells(row, "J").Formula = "=IF(B7=""B""), ((F7D7) * C7 * 50)  I7, ((F7D7) * 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.

Jul 20th, 2008, 10:40 AM
#2
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.
(2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015) . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums
Classic VB FAQs (updated Oct 2010) ...Database Development FAQs/Tutorials (updated May 2011)
^{(includes fixing common VB errors)} .......... ^{(includes fixing common DB related errors, and [Classic VB] ADO tutorial /further steps, and [VB.Net] ADO.Net Tutorial)}.
Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker
Connection strings .•. MDAC/Jet/ACE downloads .•. SQL Server downloads .•. MZTools (free upgrade for the VB6/VBA Editor)

Jul 20th, 2008, 11:12 AM
#3
Thread Starter
PowerPoster
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.

Jul 20th, 2008, 09:24 PM
#4
Thread Starter
PowerPoster
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.

Jul 21st, 2008, 01:25 AM
#5
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"", ((F7D7) * C7 * 50), ((F7D7) * 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
Microsoft MVP: 2011  2015 IMP Links : Acceptable Use Policy, FAQ
MyGear:
ACER R7 (Win 8.1+Office 2013+VS2013)  Sony VPCCB45FN with a Win10+Office 2010.  Mac Book Pro (10.6.8) with Office 2011

Jul 21st, 2008, 06:55 AM
#6
Junior Member
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"", ((F7D7) * C7 * 50), ((F7D7) * C7 * 50))"
 No loops required.
 No copy and paste.
HTH,
Colin
Last edited by Colin_L; Jul 21st, 2008 at 07:14 AM.

Jul 21st, 2008, 09:01 AM
#7
Thread Starter
PowerPoster
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

Jul 21st, 2008, 09:07 AM
#8
Re: Insert Excel Formula With Relative Reference From VB
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
Microsoft MVP: 2011  2015 IMP Links : Acceptable Use Policy, FAQ
MyGear:
ACER R7 (Win 8.1+Office 2013+VS2013)  Sony VPCCB45FN with a Win10+Office 2010.  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

Forum Rules

Click Here to Expand Forum to Full Width
Survey posted by VBForums.
