.Formula vs .FormulaR1C1 in 2007

[MacGuy]

Here's the problem. I have VBA inputting this formula in the same column
(col H) of cells:

..Formula =
"=if(trim(rc4)=""MS"",""MNGTS"",if(trim(rc4)=""MN"",""MAINS"",""""))"

For a single user on Excel 2007 it appears in the cell as:

=if(Trim(RC4)="MS","MNGTS",if(trim(RC4)="MN","MAINS",""))

When I change the .Formula to .FormulaR1C1 then the (RC4) becomes ($D2) on
row 2, which is correct.

The issue is other users of the same template with Excel 2007 don't have
this problem.

I also put in (rc[-4]), which also works, but I need to specifically
reference Col D so the column that has the formula may change.

Our office is slowly migrating to 2007 and since I do all the VBA
programming I'm still on 2003... Is there some setting that needs changing
in '07?

 

[Barb Reinhardt]

Is it possible that the one user has their default settings for formulas set
to R1C1 notation? Have them check under EXCEL OPTIONS -> Formulas -> R1C1
reference style is checked under Working with formulas.

In R1C1 reference style, if the formula is in row 2, RC4 converts to $D2.
What exactly do you want?

 

[Barb Reinhardt]

Also, might that cell be formatted as TEXT on the one users machine?

 

[Dave Peterson]

It doesn't matter what the user is showing. It matters how you're creating the
formula.

If you're using R1C1 reference style (and you are), then you should use
..formular1c1

If you're using A1 reference style, then you should use .formula

Sometimes, excel will guess what you meant and fix the formula. Sometimes, it
won't. I don't know the rules it uses to determine how bad the formula is, so I
never let excel guess. I'll just use the correct property (.formula or
..formular1c1).

Here's the problem. I have VBA inputting this formula in the same column
(col H) of cells:

.Formula =
"=if(trim(rc4)=""MS"",""MNGTS"",if(trim(rc4)=""MN"",""MAINS"",""""))"

For a single user on Excel 2007 it appears in the cell as:

=if(Trim(RC4)="MS","MNGTS",if(trim(RC4)="MN","MAINS",""))

When I change the .Formula to .FormulaR1C1 then the (RC4) becomes ($D2) on
row 2, which is correct.

The issue is other users of the same template with Excel 2007 don't have
this problem.

I also put in (rc[-4]), which also works, but I need to specifically
reference Col D so the column that has the formula may change.

Our office is slowly migrating to 2007 and since I do all the VBA
programming I'm still on 2003... Is there some setting that needs changing
in '07?

 

[MacGuy]

Barb,

thanks for the responses. I perform a text-to columns general format prior
to inputting the formula. I also checked the R1C1 reference style which is
not selected. I did turn it on to see the result but I'm getting the same
result, just in the R1C1 style.
--
MacGuy

Also, might that cell be formatted as TEXT on the one users machine?
--
HTH,

Barb Reinhardt

Here's the problem. I have VBA inputting this formula in the same column
(col H) of cells:

.Formula =
"=if(trim(rc4)=""MS"",""MNGTS"",if(trim(rc4)=""MN"",""MAINS"",""""))"

For a single user on Excel 2007 it appears in the cell as:

=if(Trim(RC4)="MS","MNGTS",if(trim(RC4)="MN","MAINS",""))

When I change the .Formula to .FormulaR1C1 then the (RC4) becomes ($D2) on
row 2, which is correct.

The issue is other users of the same template with Excel 2007 don't have
this problem.

I also put in (rc[-4]), which also works, but I need to specifically
reference Col D so the column that has the formula may change.

Our office is slowly migrating to 2007 and since I do all the VBA
programming I'm still on 2003... Is there some setting that needs changing
in '07?

 

[MacGuy]

Dave,

Thanks for the reply. I don't like excel guessing for me either and if I
had known this I wouldn't have used .formula. What's gets me is I have 50+
users who don't have the same problem. Got some work to do.

Thanks again.
--
MacGuy

It doesn't matter what the user is showing. It matters how you're creating the
formula.

If you're using R1C1 reference style (and you are), then you should use
..formular1c1

If you're using A1 reference style, then you should use .formula

Sometimes, excel will guess what you meant and fix the formula. Sometimes, it
won't. I don't know the rules it uses to determine how bad the formula is, so I
never let excel guess. I'll just use the correct property (.formula or
..formular1c1).

Here's the problem. I have VBA inputting this formula in the same column
(col H) of cells:

.Formula =
"=if(trim(rc4)=""MS"",""MNGTS"",if(trim(rc4)=""MN"",""MAINS"",""""))"

For a single user on Excel 2007 it appears in the cell as:

=if(Trim(RC4)="MS","MNGTS",if(trim(RC4)="MN","MAINS",""))

When I change the .Formula to .FormulaR1C1 then the (RC4) becomes ($D2) on
row 2, which is correct.

The issue is other users of the same template with Excel 2007 don't have
this problem.

I also put in (rc[-4]), which also works, but I need to specifically
reference Col D so the column that has the formula may change.

Our office is slowly migrating to 2007 and since I do all the VBA
programming I'm still on 2003... Is there some setting that needs changing
in '07?