Formula Showing

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What's your code? It's going to be something very simple but without seeing it who knows?

Regards,
D
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top