Relative Formula References NOT changing After Insert From VB

[David]

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

 

[macropod]

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.

 

[David]

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?

========================
RE:
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.

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]

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

 

[macropod]

Hi David,

AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced A1 notation.

The manual method of inserting a formula into one cell, then dragging it down a column, is equivalent to copying the cell's contents
and pasting them into the subsequent cells. That wasn't what your code was doing, though - it was putting a specific formula into
each cell.
As for your question, the R1C1 reference will create the offsets needed for each row even if you enter that same formula (via VB)
into each cell. R1C1 notation can also be used to create absolute and mixed relative/absolute addressing (eg =R6C[-1]., when entered
into either C4 or C10 would equate to =B$6, because a specific row is designated).

Another way of implementing the code, without resorting to R1C1 notation would be to use something like:
With oWB.ActiveSheet
.Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit
End With
though, as I noted in my previous post, you'll still need to address the fact that your code uses the same calculation for both the
true and false results of the IF test.


--
Cheers
macropod
[MVP - Microsoft Word]

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?

========================
RE:
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.

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]

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

 

[David]

Thanks for response.

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.

This is the similar to your second solution except I used formula.

strToken1 = "=IF(B" & CStr(row) & "=""B"","
strToken2 = "((F" & CStr(row) & "-D" & CStr(row) & ")"
strToken3 = " * C" & CStr(row) & " * 50)"

strText = strToken1
strText = strText & strToken2 & strToken3 & ", "
strText = strText & strToken2 & strToken3 & ")"
.Cells(row, "H").Formula = strText

Will again look at your first to see if using offsets are faster or easier
to implement.

===================
MultiPlan -- forgot about that.

Have a nice day, and again thanks for your efforts on my behalf.

David

Hi David,

AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced
A1 notation.

The manual method of inserting a formula into one cell, then dragging it
down a column, is equivalent to copying the cell's contents and pasting
them into the subsequent cells. That wasn't what your code was doing,
though - it was putting a specific formula into each cell.
As for your question, the R1C1 reference will create the offsets needed
for each row even if you enter that same formula (via VB) into each cell.
R1C1 notation can also be used to create absolute and mixed
relative/absolute addressing (eg =R6C[-1]., when entered into either C4 or
C10 would equate to =B$6, because a specific row is designated).

Another way of implementing the code, without resorting to R1C1 notation
would be to use something like:
With oWB.ActiveSheet
.Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit
End With
though, as I noted in my previous post, you'll still need to address the
fact that your code uses the same calculation for both the true and false
results of the IF test.


--
Cheers
macropod
[MVP - Microsoft Word]

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?

========================
RE:
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.

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]


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

 

[macropod]

Hi David,

I think you'll find this implementation:
..Cells(row, "H").Formula = "=(F" & row & "-D" & row & ")*C" & row & "*50"
somwhat faster, since the IF test is pointless and this construction avoids the CStr conversion and string building/concatenation
steps. Your workbook should also end up smaller and faster at recalculating too.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for response.

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.

This is the similar to your second solution except I used formula.

strToken1 = "=IF(B" & CStr(row) & "=""B"","
strToken2 = "((F" & CStr(row) & "-D" & CStr(row) & ")"
strToken3 = " * C" & CStr(row) & " * 50)"

strText = strToken1
strText = strText & strToken2 & strToken3 & ", "
strText = strText & strToken2 & strToken3 & ")"
.Cells(row, "H").Formula = strText

Will again look at your first to see if using offsets are faster or easier to implement.

===================
MultiPlan -- forgot about that.

Have a nice day, and again thanks for your efforts on my behalf.

David

Hi David,

AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced A1 notation.

The manual method of inserting a formula into one cell, then dragging it down a column, is equivalent to copying the cell's
contents and pasting them into the subsequent cells. That wasn't what your code was doing, though - it was putting a specific
formula into each cell.
As for your question, the R1C1 reference will create the offsets needed for each row even if you enter that same formula (via VB)
into each cell. R1C1 notation can also be used to create absolute and mixed relative/absolute addressing (eg =R6C[-1]., when
entered into either C4 or C10 would equate to =B$6, because a specific row is designated).

Another way of implementing the code, without resorting to R1C1 notation would be to use something like:
With oWB.ActiveSheet
.Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _
"((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit
End With
though, as I noted in my previous post, you'll still need to address the fact that your code uses the same calculation for both
the true and false results of the IF test.


--
Cheers
macropod
[MVP - Microsoft Word]

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?

========================
RE:
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.


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]


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