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

Jumat, 05 September 2014

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@yahoo.com [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@yahoo.com [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@hotmail.com [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

__._,_.___

Posted by: Green <1z@compuserve.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

----------------------------------
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

----------------------------------

Visit Your Group