Formula Showing

[Matt]

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

[dk]

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

Regards,
D

[Anne Troy]

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.

[Matt]

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

[dk]

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

[Matt]

Thanks thats worked