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

 

David and Paul, you both nailed it, of course.   The destination cell WAS formatted as text.   Thanks for your help!

Paul, I inserted your ...NumberFormat = "General" statement, and, presto, the inserted formula did what I intended.   

I appreciate the replies from others, too, but David and Paul zeroed in why my code wasn't doing what I wanted.

Dunbar

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

I created a workbook and tried to duplicate your issue.

If the target cell is formatted as Text, then I get your result.

If I change it to "General", it works as expected.

 

So, I added this to your VBA:

    Worksheets("PlayList").Range("CustomName1").Offset(0, s - 1).NumberFormat = "General"

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

 

so that it "forces" the cell formatting.

 

if that doesn't work for you, then can you send me your file (or a portion) so I can take a look?

 

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

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

 

__._,_.___

---

Posted by: h_dunbar@hotmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

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

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___