.Formula vs .FormulaR1C1 in 2007

  • Thread starter Thread starter MacGuy
  • Start date Start date
M

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?
 
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?
 
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?
Click to expand...
 
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


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

Barb Reinhardt



MacGuy said:
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?
Click to expand...
Click to expand...
 
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


Dave Peterson said:
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?
Click to expand...
Click to expand...
 
Back
Top