VBA Quotations

[ExcelRoy]

Hi Guys,

I am having some problems in using the quotations in VBA

I need to generate the following

=IF(VP1_T_01=0,"","£" & VP1_T_01 & "m")

I have a very poor effort of

Cells(i, "G").Formula = """"=IF(VP1_T_0" & x & "=0,"""","£" VP1_T_0" & x &")""""

I know its the amount of quotations, but i just cant get the amount in the right places

Thanks

 

[shg]

Record a macro inserting the formula.

 

[ExcelRoy]

Yup, thats the way, sorry

Got the code now, thanks

 

[ExcelRoy]

Actually found this won't work as i had hoped

i need the "x" values in the code. How would i achieve that using the recorder?

Thanks

 

[shg]

In general,

o Replace each quote with double quotes

o Add single quotes at the beginning and end

 

[ExcelRoy]

I have tried replacing the quotes with double quotes etc but still nothing?

 

[Rick Rothstein]

The rule is actually quite simple... one quote mark goes around the entire text constant (that tells VB what is inside is a text constant) and for each quote mark that is part of the text you just surrounded with quote marks, put an extra quote mark next to it so that there are two of them next to each other... remember, you double up each original quote mark that was part of the text (excluding the quote marks you added to tell VB that it was processing a text constant). For example, if this is your text...

The man said "Hello" to me.

it would become this...

"The man said ""Hello"" to me"

The purple ones were added to tell VB it was processing a text constant and the orange ones were where I doubled up the original quote marks.

 

[ExcelRoy]

Hi Rick,

Thanks, but using this method, what am i doing wrong here

.Formula = "=IF(V_P1_T_0"" & x & ""=0"","""",""£"" & ""V_P1_T_0"" & x & ""m""& "")"

 

[shg]

Original:

=IF(VP1_T_01=0,"","£" & VP1_T_01 & "m")

Double the quotes:

=IF(VP1_T_01=0,"""",""£"" & VP1_T_01 & ""m"")

Add quotes at beginning and end

"=IF(VP1_T_01=0,"""",""£"" & VP1_T_01 & ""m"")"

 

[ExcelRoy]

Hi shg,

Yes this works, but i need it to work with my value x in my last code

Thanks