How to code an IF formula in VBA with text results and display actual formula in cell

Hi all,

I wonder if anyone could shed any light on the below.

I would like to construct an IF formula in VBA, so that the formula is displayed in the cell itself and not just the result. Certain results in the IF formula are text and Excel requires that they are enclosed in "". The probelm is, when my formula is displayed on the worksheet, the text responses are not enclosed in "", resulting in a #NAME? error. This will be easier to understand with an example.

My code is:

Please Login or Register  to view this content.

In the cell on the worksheet this results in:

Formula:

Please Login or Register  to view this content.

IN, OK, Diff = and N/A need to be enclosed in "".

What I am after, therefore, is:

Formula:

Please Login or Register  to view this content.

I have tried placing extra "" around the text I want as my result, but this does not work.

Thanks in advance,

Wouldn't this do it (untested):

Please Login or Register  to view this content.

Please click the * below if this helps

In VBA, your formula is a text string surrounded by quotes. To define a literal quote within a text string, use two consecutive quotes for each literal quote you want to define.

Please Login or Register  to view this content.

Thanks for your suggestion JasperD. Unfortunately, this results in a run time error '1004' Application-defined or Object-defined error.

Originally Posted by JasperD

Wouldn't this do it (untested):

Please Login or Register  to view this content.

AlphaFrog, your solution worked. I figured it would be something like this, but I hadn't bee able to get it to work successfully. Thinking about it, where I was going wrong is that I was splitting the VBA code into several text strings instead of one text string.

In VBA, your formula is a text string surrounded by quotes. To define a literal quote within a text string, use two consecutive quotes for each literal quote you want to define.

Please Login or Register  to view this content.