i want to chnge form Formula.R1C1 in VBA to normal Formula

[MyExcel]

Hi All
i have problem when i record Macros
when i use Formula the Recording will give me Formula like this
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
i want Excel Automatic use the other format
ActiveCell.Formula = "=SUM(b2:b8)"
i searched throw the option of VBA Editor but i couldn't find the solve for it
Note : i use Normal formula in Excel Not R1C1 formla i don't check it in Option of excel
but in VBA it works Automaticlly
i hope to find the Soluation

 

[DonkeyOte]

When the formula is written to the cell it will reflect your settings (ie will appear as SUM(B2:B8))

 

[MyExcel]

thanx DonkeyOte
but i told u my setting in normal excel isn't use the R1C1
but this Problem happen When i Record any macro and use any kind of Formula in it

 

[DonkeyOte]

Have you actually checked the formula written to your spreadsheet ????

 

[Andrew Poulsom]

The macro recorder always uses R1C1 reference style. As far as I know there is no way to make it use the Formula property.

 

[MyExcel]

Thanx DonkeyOte and Andrew Poulsom
it is hard to understand the R1C1
Thanx All

 

[DonkeyOte]

Just to elaborate re R1C1 - in VBA most coders use an R1C1 type approach to handling ranges etc - eg Cells(10,1) = A10... this is because iterating beyond the letter Z to AA can be complex whereas going from column 26 to 27 is not.

So given it's Row then Column and the references are relative to the cell in which the reference will reside and the fact that anything appearing within [ ] is an adjustment to the relative value the below formula pasted in B9

=SUM(R[-7]C:R[-1]C)

can be analysed such that

R[-7] in B9 results in row 2, R[-1] results in row 8.

anything appearing after the R or C that is not encased within [ ] is absolute reference (thus not relative) so the following in B9:

=SUM(R7C:R[-1]C)

means sum from row 7 to row 8 ... if that formula were pasted in B10 rather than B9 it would result in a sum from row 7 to row 9 -- the 9 adapts as it is relative R[-1] but the 7 remains constant: R7

I hope that helps a little.

Don't be afraid of using R1C1 approach in VBA -- in the long run it will make your life easier.

 

[MyExcel]

Thanx DonkeyOte
it is great Explain
i will try to read more and more about R1C1
it will help me to understand it better than now
Thanx again DonkeyOte