Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formula Showing

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is probably very simple but very frustrating at the moment. I am entering a formula through a macro into a template and the cell is showing the formula rather than the value. I have looked in options to see if formula is checked in the view option and it's not. Someone please help

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    What's your code? It's going to be something very simple but without seeing it who knows?

    Regards,
    D


  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It sounds like either the format of the cell is text, or your code is creating the text format on it. The cell should be formatted as General.
    ~Anne Troy

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the code that is outputting the formula rather than values.

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=concatenate(A3,IV1)"

    However the following code is used in a different cell in the same spreadsheet and the values are output

    Range("IV2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""yyyy-dd-mm"")"
    Range("A1").Select

    The element of the worksheet that is showing formulas rather than values has been copied from another workbook with the following code, could this be the reason?

    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    Thanks

    Matt

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the code that is outputting the formula rather than values.

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=concatenate(A3,IV1)"

    However the following code is used in a different cell in the same spreadsheet and the values are output

    Range("IV2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""yyyy-dd-mm"")"
    Range("A1").Select

    The element of the worksheet that is showing formulas rather than values has been copied from another workbook with the following code, could this be the reason?

    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    Thanks

    Matt

    Hi Matt,

    You're using ActiveCell.FormulaR1C1 which expects an address in the relative notation e.g. this will put a formula in A2 equivalent to Concatenate(A3,IV1)

    Sub test()
    Range("A2").Select
    'Put the concatenate formula in using relative cell referencing
    ActiveCell.FormulaR1C1 = "=concatenate(r3c,r1c256)"
    End Sub

    If you want to use absolute references (i.e. A1, C5, IV10 notation) then use this:-

    Sub test()
    Range("A2").Select
    'Put the concatenate formula in using relative cell referencing
    ActiveCell.Formula = "=concatenate(A3,IV1)"
    End Sub

    HTH,
    D

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks thats worked

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •