.Formula vs .FormulaR1C1 in 2007
in [Programming]
|
Prev: Move data from Doc1 to Doc2, end with 2nd doc open
Next: prevent user from deleting a tab prevent running macro from menu
From: MacGuy on 25 Feb 2010 11:22 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
From: Barb Reinhardt on 25 Feb 2010 12:01 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? -- HTH, Barb Reinhardt "MacGuy" wrote: > 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
From: Barb Reinhardt on 25 Feb 2010 12:01 Also, might that cell be formatted as TEXT on the one users machine? -- HTH, Barb Reinhardt "MacGuy" wrote: > 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
From: Dave Peterson on 25 Feb 2010 12:58 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). MacGuy wrote: > > 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 Peterson
From: MacGuy on 25 Feb 2010 13:07
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" wrote: > Also, might that cell be formatted as TEXT on the one users machine? > -- > HTH, > > Barb Reinhardt > > > > "MacGuy" wrote: > > > 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 |