Jumat, 05 September 2014

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: Mark Bacheldor <m_bacheldor@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
----------------------------------
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