Hi David,

That's because you're telling Excel to use the same formula on every row.

Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the
formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for
example, the first entry would be in H7 (R7C8), the code would look like:
oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))"
However, if the first entry would be in H10 (R10C8), the code would look like:
oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 =
"=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))"

I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double
quotes around it.

--
Cheers
macropod
[MVP - Microsoft Word]


"David" wrote in message ...
I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel).

The formulas are column specific and are
relative reference formulas. For example

oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit
.
After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the
same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row.

QUESTIONS

1) What am I doing wrong?

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

What's the best way to handle Formulas when creating a new Workbook from VB:

1) Same as above using Formula Property

2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another

3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest.

4) Other

Thanks