For a start, when you are inserting a formula into a cell, you should use
Formula, not Value
> Range("B1").Formula = "= ""Ripe "" & A1 & "" Cheap"""
That way Excel will know to think of it as a formula.
Then you have
"= S1 & A1 & ""Cheap"""
and indicated that you got a #Name error. You shouldn't have. S1 is a cell
reference, in the same way that A1 is. Is this the actual code that gave
you the #Name reference? If not, please post the actual code.
Regarding VBA variables ... do not use names that look like cell references.
E.g. S1 is a bad variable name, while MyString1 can't be confused with a
cell reference, so is better that way, although it still doesn't tell you
anything about what is in the variable.
Now you can certainly put the contents of a variable into a formula, e.g.
> Range("B1").Formula = "= """ & MyString1 & """ & A1 & "" Cheap"""
would do it, as long as MyString1 doesn't have any quotes in it.
Not sure why you'd want to do it, though. The code ends up being longer,
and less obvious.
Perhaps you want to select from several possibilities for that first word?
If so, then you'd need more code than this ... perhaps an array to hold the
words and a selector to randomly choose one.
Regards, Dave S
----- Original Message -----
From: "mickey11030" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Wednesday, March 28, 2012 4:12 AM
Subject: [ExcelVBA] Using Literals in Formulas
> Assume Col A has a list of fruit names, and A1 contains "Apples". I can
> use the following code
> Range("B1").Value = "= ""Ripe "" & A1 & "" Cheap"""
> to get "Ripe " & A1 & " Cheap" which displays as Ripe Apples Cheap in B1.
> The contents of B1 can be copied down Col B giving Ripe Watermelon Cheap,
> etc. in Col B.
>
> But suppose instead of Ripe I have a long string. I'd like to define a
> variable, S1 to be that string, and enter something like "= S1 & A1 &
> ""Cheap""" into B1, but I can't figure out how to do that. When I finally
> got the quotation marks right to avoid a syntax error, S1, not the literal
> it represents, was entered and that was flagged as a #Name error.
>
> I realize I could enter that long literal into a cell, and simply
> reference that cell, but I wonder if there is any way other than that.
>
> Thanks in advance
>
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1424 / Virus Database: 2113/4897 - Release Date: 03/27/12
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar