Formula with & and a single quote 2

[Andrzejek]

Trying to write a formula that will give me in the cell:
strStepValue = "'" & !PT_COLOR.Value & "'"

PT_COLOR will come from cell C5, so my formula is:
="strStepValue = & !" &C5& ".Value & ""'"""
but that gives me strStepValue = & !CA_SOL_DISP.Value & "'" in the cell

I have managed to include "'" (double quote, single quote, double quote) at the end, but how do I get the other "'" in the middle of the formula?

 

[strongm]

Not quite sure I completely understand what you are trying to do

 

[Andrzejek]

As you can see this is me trying to write VBA code by formula in Excel.
I have about 40 different projects coming up, and they all include variable number of fields from DB.
So I was hoping to list those DB fields in Excel, write some formulas so I can re-do it over and over for different projects.

A lot easier (I hope) than trying to keep all of that straight by coding 'by hand'.

Edit: I may just as well do it thru a VBA code instead, it may be a lot simpler

 

[Deniall]

Can you utilise Excel's CHAR() function? CHAR(34) will give you a " and CHAR(39) will give you a '.

 

[OzFoxy]

Hi Andrzejek,
Trying to write a formula that will give me in the cell:
strStepValue = "'" & !PT_COLOR.Value & "'"

Deniall has hit the nail on the head.
I'm confused about 'PT_COLOR' and 'CA_SOL_DISP' so I have assumed that the content of C5 is as shown on the attachment.
The formula in B2 is ="strStepValue = "&CHAR(34)&"'"&CHAR(34)&" & !"&TRIM(C5)&".Value & "&CHAR(34)&"'"&CHAR(34)
The formula in B5 is ="strStepValue = "&CHAR(34)&char(39)&CHAR(34)&" & !"&TRIM(C5)&".Value & "&CHAR(34)&CHAR(39)&CHAR(34)
Both appear to provide the content you want in the cell.