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 14:16
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" wrote:

> 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
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Move data from Doc1 to Doc2, end with 2nd doc open
Next: prevent user from deleting a tab prevent running macro from menu