Formula appears as text in cell??? - Xtreme Visual Basic Talk

07-02-2012, 08:37 AM

dotnetme

Regular

Join Date: May 2007

Location: NJ

Posts: 50

Formula appears as text in cell???

I'm inserting data into excel sheets and after i fill each sheet I want to sum a column so select the data and add a formula to a cell. However when the sheet is opened the cell contains the formula as text instead of the sumation value. I tired several ways but here are a few different meathods that both end up with the same undesired result.

Code:

'Meathod 1
oSheet.Range("H2:H" & (dtEntries.Rows.Count + 2).ToString).Select()
oSheet.Range("H" & (dtEntries.Rows.Count + 2).ToString).FormulaR1C1= "=SUM(R[-" & (dtEntries.Rows.Count).ToString & "]C:R[-1]C)"

'Meathod 2
oSheet.Range("H" & (dtEntries.Rows.Count + 2).ToString).Formula = "=SUM(H2:H" & ((dtEntries.Rows.Count + 1).ToString).ToString & ")"

'Meathod 3
oSheet.Range("H" & (dtEntries.Rows.Count + 2).ToString).Activate()
oExcel.ActiveCell.Formula = "=SUM(H2:H" & ((dtEntries.Rows.Count + 1).ToString).ToString & ")"

I'm I missing something or do I need to do something special???

VB 2008 (VS 2008)
Excel 2003
Windows XP SP3

Thanks in advance for and help

dotnetme

07-02-2012, 11:02 AM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,739

In the line preceding your .Formula (any syntax), add:

Code:

oSheet.Range("H" & (dtEntries.Rows.Count + 2).ToString).NumberFormat = "General"

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

Last edited by Cerian Knight; 07-02-2012 at 12:11 PM. Reason: Added missing oSheet. to code

07-02-2012, 11:47 AM

dotnetme

Regular

Join Date: May 2007

Location: NJ

Posts: 50

That Worked!!!

I didn't think of that. Thanks for your help!!

dotnetme