Results 1 to 6 of 6
Like Tree1Likes
  • 1 Post By Rick Rothstein

How to paste a script in a given cell

This is a discussion on How to paste a script in a given cell within the Excel Questions forums, part of the Question Forums category; Hello people, I think I have a pretty dumb question but I can't figure it out. I want to know ...

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    30

    Default How to paste a script in a given cell

    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!

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default Re: How to paste a script in a given cell

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    27,755

    Default Re: How to paste a script in a given cell

    Quote Originally Posted by Nahuster View Post
    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's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    27,755

    Default Re: How to paste a script in a given cell

    Quote Originally Posted by Eric W View Post
    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.
    Last edited by Rick Rothstein; Jan 6th, 2017 at 01:40 PM.
    Eric W likes this.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  5. #5
    New Member
    Join Date
    Apr 2016
    Posts
    30

    Default Re: How to paste a script in a given cell

    THANK YOU VERY MUCH. That Worked!


    Quote Originally Posted by Eric W View Post
    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.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,346

    Default Re: How to paste a script in a given cell

    Glad we could help.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com