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
=======================================
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.
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:
Quote:
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?
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.
Re: Insert Excel Formula With Relative Reference From VB
Quote:
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?
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
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
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...