0

I'm using VBA to introduce formulas in my local database regarding some conditions. Here is an example of the code I'm using:

Range("CP" & l).Formula = "=IFERROR(((T" & l & "*6)-SUM(U" & l & ":Z" & l & "))/(T" & l & "*6);"""")"

Were "l" is my first blank row (it's working)

The formula is working very well, but the problem I have is that the code is returning only the value of the result in my cell, but I would like to see the whole formula in my cell.

Is it something that can be possible to do ?

* The real problem is that the formula IS NOT in the formula bar. The Code write the score, but not the formula. If I do a test with 

Range("CP" & l).Formula = "=((T" & l & "*6)-SUM(U" & l & ":Z" & l & "))/(T" & l & "*6)"

I see the formula in the formula bar.

I really don't understand...

Thanks :)

Marie

Community
  • 1
  • 1
Marie-Andrée Morin
  • 1
  • 2
  • 1
    If you select the cell, do you not see the formula in the formula bar? Do you simply want to *display* the formula, rather than its evaluated result? – David Zemens Oct 17 '13 at 14:21
  • 1
    Before the post was edited there was an extra space before the equal sign: `" =IFERROR(` this would output text and not the actual formula. – Automate This Oct 17 '13 at 14:27
  • 1
    try doing a bit of reverse engineering based on [*`this`*](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868) –  Oct 17 '13 at 14:28
  • Hi ! David Zemens wrote "If you select the cell, do you not see the formula in the formula bar?" I don't see the formula in the formula bar, and it's exactly my problem ! Thanks again for your help :) – Marie-Andrée Morin Oct 17 '13 at 15:53
  • I can't think of any reason how the formula would not appear in the formula bar, unless you have another part of code that is replacing the evaluation with the `.Value`. I see Sid is troubleshooting with you, so I will leave it to him. – David Zemens Oct 17 '13 at 18:59
  • No, the cell doesn't have any protection. I have an error message when using IFERROR ... seems that the Code is taking it as a part of the code, not a string to copy in te cell. I've try too with .Value with the same result ... – Marie-Andrée Morin Oct 17 '13 at 19:16

1 Answers1

3

but I would like to see the whole formula in my cell.

To display the formula in the cell instead of the values, you have 3 options

  1. Format the cells as text and then use the code which you have.

    Range("CP" & l).Formula = "=IFERROR(((T" & l & "*6)-SUM(U" & l & ":Z" & l & "))/(T" & l & "*6);"""")"

  2. Change .Formula to .Value and add a ' before the = sign

    Range("CP" & l).Value = "'=IFERROR(((T" & l & "*6)-SUM(U" & l & ":Z" & l & "))/(T" & l & "*6);"""")"

  3. Activate the option File Tab | Options | Advanced | "Show Formulas in cells instead...."

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250