Jumat, 05 September 2014

Re: [ExcelVBA] Trouble inserting a formula into a cell

 

Right, Dave S.   I had it like you suggest in one of my earlier tries, but then I wondered if 26 had to be set off in some way numerically by itself.   I was clearly wrong about that.


Thanks,
Dunbar


---In ExcelVBA@yahoogroups.com, <smartware.consulting@...> wrote :


There are no double double quotes in this statement, so Chr$(34) is not applicable.
 
There is one unnecessary set of & concatenations, though.
 
"=IF(SeriesGame" & s & "=" & 26 & ", MyGameName,)"
 
could be given as
 
"=IF(SeriesGame" & s & "=26, MyGameName,)"
 
and would become a bit easier to read.
 
I agree with the earlier responses, the problem will be that the destination cell is formatted as Text.

Regards, Dave S
 
----- Original Message -----
Sent: Saturday, September 06, 2014 1:38 AM
Subject: Re: [ExcelVBA] Trouble inserting a formula into a cell

I tend to set a variable to Chr$(34) and use that rather then have all of those double double quotes.

LIsa


-----Original Message-----
From: 'L. Allen' loaa3406@... [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Fri, Sep 5, 2014 4:34 pm
Subject: RE: [ExcelVBA] Trouble inserting a formula into a cell



I agree with Mark that 'vlookup' may work better.

But for your formula insertion you have to be wary of your use of double quotes and commas.
"=IF(SeriesGame" & s & "=" & 26 & ", MyGameName,)"

Is SeriesGame cell text or numeric?  If it's text, i.e. ABC then you're looking to compare abc2 to 26.

Then the comma following MyGameName should either be followed by double quotes to do nothing or removed.

When you type the formula in, Excel is probably correcting it for you.

Strictly my opinion, good luck.
Grandpa Al

From: Mark Bacheldor m_bacheldor@... [ExcelVBA]
Sent: ‎9/‎5/‎2014 6:31 AM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Trouble inserting a formula into a cell

 
Why would you not use a vlookup?


From: "h_dunbar@... [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Friday, September 5, 2014 6:33 AM
Subject: [ExcelVBA] Trouble inserting a formula into a cell

 
I'm trying to have vba insert the formula

=IF(SeriesGame2=26, MyGameName,)

into an Excel cell.

SeriesGame2 and MyGameName are named cells on another worksheet in the same workbook.

Here's the vba code I'm using:

    Worksheets("PlayList").Range("CustomName1").Offset(0, s - 1).Formula = "=IF(SeriesGame" & s & "=" & 26 & ", MyGameName,)"

s is an integer that does = 2 when the above vba statement is reached.

When my vba statement is executed, it puts my intended formula into the desired cell, but it's not treated as a formula, it's treated as text.  (that is, the cell displays the formula exactly as written above.)

If I copy and paste that cell to another blank cell, it's still treated as text, regardless of whether I PasteSpecial 'values' or 'formulas'.   But if I retype it into the next cell below, it works the way I intended.   Flipping back and forth between the 2 cells, there is no difference in the formula bar, but one cell shows the text, while the other cell evaluates the formula as I intended.

I have almost posted questions to this group 10-20 times in the past year, but have always found my answer by Googling the question.   This one has me stumped.

I'm using Excel 2003, but would like this to work for more recent versions, too.

Thanks for any help!

Dunbar
 





No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1432 / Virus Database: 4015/7657 - Release Date: 09/05/14

__._,_.___

Posted by: h_dunbar@hotmail.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)
----------------------------------
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