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

 

[Rick Rothstein]

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""& "")"

Okay, I need to expand my explanation a little bit. When I referred to a text constant, I meant each one you had individually. When you concatenate text together each part being concatenate (that is, the beginning and ending ones plus each one appearing between & signs). So, if you had these two text string...

The boy said "Hello" first
he said "Good-bye" next

and let's say you had a variable named Splice that contained the text " and then ", so the final printed out text should be...

The boy said "Hello" first and the he said "Good-bye" next

You would concatenate the original two text string and the Splice variable together like this...

"The boy said ""Hello"" first" & Splice & "he said ""Good-bye"" next"

Another way to view this would be to use individual variables for the text parts and see how my original rule is used to assign them...

Var1 = "The boy said ""Hello"" first"
Var2 = "he said ""Good-bye"" next"
Splice = " and then "

Concatenating these together yields...

Var1 & Splice & Var2

Now look what you get if you replace Var1 and Var2 with what they are equal to...

"The boy said ""Hello"" first" & Splice & "he said ""Good-bye"" next"

but this is just what we got above when we applied the rule I originally gave you to each text constant individually. Now, one more thing I would like to show you as it tends to be confusing the first time come across it.. when there is a quote mark at the beginning or end of an individual text string as in this example...

The boy said "Hello"

First, double up the existing quote marks...

The boy said ""Hello""

and now surround it with the quote marks that tell VB it is processing a text constant...

"The boy said "'Hello"""

Note what you end up with when the original text has a quote mark at the end (or beginning for that matter) of the text string... you end up with 3 quotemarks... the two that replaced the original one that was part of the text and a third one for the quote mark that delineates the text constant to VB.

Hopefully this has helped you some. Normally, I would show you the corrected text string for the formula you asked about, but I am not sure what that formula should be as you seem to have left something out. As posted, and if I were able to correct the quote marks for you, you would be left with this as part of your formula...

"£"V_P1_T_0

a quoted £ symbol followed by text that does not look like it should be next to that symbol. If you cannot get the quote marks straightened out on you own, then post the actual formula you expect to be in the cell along with what you think the VB assignment should be and I believe someone here would then be able to show you the correct method to perform you concatenation.

 

[ExcelRoy]

Wow!

Thanks Rick,

I understand the basics of the way it should work, but with so many arguments it is a little difficult to follow

Please find below my complete code

Sub AutoFormulaEntry()
x = 1
For i = 14 To 1895 Step 19
    Cells(i, "G").Formula = "=IF(Value_P1_Team_0"" & x & ""=0"","""",""£"" & ""Value_P1_Team_0"" & x & ""m""& "")"
    x = x + 1
Next i
End Sub

Basically what it does is enter the dsired formula using the x as an incremented number

i would hope the outcome would read

=IF(Value_P1_Team_01=0,"","£" & Value_P1_Team_01 & "m")
=IF(Value_P1_Team_02=0,"","£" & Value_P1_Team_02 & "m")
=IF(Value_P1_Team_03=0,"","£" & Value_P1_Team_03 & "m")

etc

Hope this makes sense

 

[Rick Rothstein]

Let's do your formula in stages. Here is the original formula (with extra spaces removed as they tend to get in the way of the process I'm about to show you)...

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

Okay, lets replace the part that will be handled by your 'X' variable with the concatenation of the variable X (the part shown in red plus the blank space in each side of it)...

=IF(Value_P1_Team_0 & X & =0,"","£"&Value_P1_Team_0 & X & &"m")

Now let's double up the quote marks (shown in purple)...

=IF(Value_P1_Team_0 & X & =0,"""",""£""&Value_P1_Team_0 & X & &""m"")

Now let's put the surrounding quote marks around the first and last text constants plus each text constant between & signs, but don't be fooled by the ampersand immediately in front of the 'm' as that is part of your text constant even though it is on the end (shown in orange)...

"=IF(Value_P1_Team_0" & X & "=0,"""",""£""&Value_P1_Team_0" & X & "&""m"")"

That's it... just assign it to the cell's formula property....

Cells(i, "G").Formula = "=IF(Value_P1_Team_0" & X & "=0,"""",""£""&Value_P1_Team_0" & X & "&""m"")"

 

[ExcelRoy]

Hi Rick,

Now that i was able to follow a little more easily.

I think what i was failing to do was single quote each section of text, in actual fact the x part of the formula made no difference once the single quotes was in place

many thanks for your help and time Rick

Much appreciated

Kind Regards

 

[Rick Rothstein]

You are quite welcome. I am glad I was able to be of some help.

One more thing which you may find useful when developing any concatenated text string with variables... use the Immediate window. Since these are text strings, you can print them out in the Immediate window while you are developing them and then just look to see if all the parts came together correctly. Just make sure you remember to assign values to the variable in the Immediate window before you execute the print statement, otherwise VB will put in the empty string (that is, nothing) in place of the variables and that could confuse things greatly.