How to paste a script in a given cell

[Nahuster]

Hello people,

I think I have a pretty dumb question but I can't figure it out.
I want to know why this works

Sheets("Template").Range("C37").Value = "=VLOOKUP(C36,RA!A2:B100,2,FALSE)"

and this doesn't

Sheets("Template").Range("C99").Value = "=LEFT(C4,FIND(" ",C4)-1)"

I guess it's because of the "" , but that's what has to go into the cell for the rest to work.

How should I phrase that?.
Again, in cell C99 I want the script to write this
=LEFT(C4,FIND(" ",C4)-1)


Thanks in advance.
Cheers!

 

[Eric W]

Try:

Sheets("Template").Range("C99").Value = "=LEFT(C4,FIND("" "",C4)-1)"

Quotes within quotes must be doubled for Excel to recognize them. If the quote is on an end, you could have 3 consecutive quotes: 2 for the interior quote, and 1 to terminate the string.

Hope this helps.

 

[Rick Rothstein]

Hello people,

I think I have a pretty dumb question but I can't figure it out.
I want to know why this works

Sheets("Template").Range("C37").Value = "=VLOOKUP(C36,RA!A2:B100,2,FALSE)"

and this doesn't

Sheets("Template").Range("C99").Value = "=LEFT(C4,FIND(" ",C4)-1)"

I guess it's because of the "" , but that's what has to go into the cell for the rest to work.

How should I phrase that?.
Again, in cell C99 I want the script to write this
=LEFT(C4,FIND(" ",C4)-1)

Quote marks on the outside of a text string tells VB that what is inside is text. VB cannot tell whether the internal quotes are text characters or your attempt to end the text string at that location. To get around this problem, quote characters need to be doubled up inside of the quote marks delineating a text string... VB assumes such doubled up quotes to represent a single quote character. Given that, your code line should be this...

Sheets("Template").Range("C99").Value = "=LEFT(C4,FIND("" "",C4)-1)"

 

[Rick Rothstein]

If the quote is on an end, you could have 3 consecutive quotes: 2 for the interior quote, and 1 to terminate the string.

The way I have explained this in the past is like this. Starting with the fact that quote marks must be doubled up inside of the outer quote marks that delineate a text string, consider this code line...

SomeVariable = "Beginning""End"

which would assign the text Beginning"End to the variable. Remove Beginning which leaves this...

SomeVariable = """End"

which is why there are three quotes when the quote mark is at the end of the text. Now, extending this one more time, remove End which leaves this...

SomeVariable = """"

which is why it takes four quote marks to represent a single quote mark character.

 

[Nahuster]

THANK YOU VERY MUCH. That Worked!

Try:

Sheets("Template").Range("C99").Value = "=LEFT(C4,FIND("" "",C4)-1)"

Quotes within quotes must be doubled for Excel to recognize them. If the quote is on an end, you could have 3 consecutive quotes: 2 for the interior quote, and 1 to terminate the string.

Hope this helps.

 

[Eric W]

Glad we could help.