Thanks for your response:

I'm creating an Excel Application from VB6.

As I recall R1C1 is Lotus notation which is OK, but haven't used for years.

Whether Excel or Lotus notation if I create an Excel workbook by hand (no VB
or VBA)
and enter a formula, then drag it down a column, Excel enters the correct
relative references in the formula.

Using .Formula or .FormulaR1C1, VB(5/6) does put the same formula into each
cell.
My ASSUMPTION that once entered Excel would correct automatically (like the
manual drag) or there is some property / method in the Excel object that I
could set / call which would generate the wanted result.

NOT exactly sure of your post.

1) Are you're saying I need to enter (via code) a different formula for each
cell in the column. I HOPE this is not correct, as why use Excel via code

OR

2) That using a R1C1 reference will create the offsets needed for each row
even if I enter that same formula (via VB) into each cell?

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

your true & false results appear to use the same formula
-- I'm aware of this

Your test for a 'B' has too many double
-- When entered from VB(5/6) the double quotes are needed around a string
literal otherwise the formula line errors.


"macropod" wrote in message
...
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