Data types?

Hi

I'm trying to sum a set of cells using
=SUM(Grid!D3+Grid!H3+Grid!L3...Grid!CR3) and I get an error message
saying 'A value used in the formula is of the wrong data type'. What
does this mean??


-- 
Derek Norman
------------------------------------------------------------------------
Derek Norman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26004
View this thread: http://www.excelforum.com/showthread.php?threadid=393659

0
8/7/2005 12:13:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
480 Views

Similar Articles

[PageSpeed] 52

Hi Derek,

The error message suggests that you are using your formula in VBA (???)
If that's true, then there are a few things for you to consider:

1) there is no SUM function in VBA. so you either use the '+' operator, the 
WorksheetFunction property, or Application. + "FunctionName", e.g:

Range("A1").Formula=1+2
Range("A1").Formula=WorksheetFunction.Sum(1,2)
Range("A1").Formula=Application.Sum(1,2)

2) you cant't use the worksheet reference format (Sheet!A1) in VBA unless 
you enclose the formula in quotation marks, e.g.:

Range("A1").Formula="=SUM(Grid!D3,Grid!H3,Grid!L3,...,Grid!CR3)"

if you want to have a VBA formula you should do somethink like this:

Range("A1").Formula=Application.Sum(Sheets("Grid").Range("D3"), _
    Sheets("Grid").Range("H3"),Sheets("Grid").Range("L3"),...,Sheets("Grid").Range("CR3"))

3) finally, the use of '+' operator within the SUM function is redundant

Regards,
KL


"Derek Norman" <Derek.Norman.1teaqa_1123419900.8495@excelforum-nospam.com> 
wrote in message 
news:Derek.Norman.1teaqa_1123419900.8495@excelforum-nospam.com...
>
> Hi
>
> I'm trying to sum a set of cells using
> =SUM(Grid!D3+Grid!H3+Grid!L3...Grid!CR3) and I get an error message
> saying 'A value used in the formula is of the wrong data type'. What
> does this mean??
>
>
> -- 
> Derek Norman
> ------------------------------------------------------------------------
> Derek Norman's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=26004
> View this thread: http://www.excelforum.com/showthread.php?threadid=393659
> 


0
8/7/2005 1:24:33 PM
Opps, the .Formula property only needs to be used in the following example:

Range("A1").Formula="=SUM(Grid!D3,Grid!H3,Grid!L3,...,Grid!CR3)"

in the rest of the ezamples it can be replaced by .Value property or just 
ommitted.

Regards,
KL


"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message 
news:up6%23mN1mFHA.3312@tk2msftngp13.phx.gbl...
> Hi Derek,
>
> The error message suggests that you are using your formula in VBA (???)
> If that's true, then there are a few things for you to consider:
>
> 1) there is no SUM function in VBA. so you either use the '+' operator, 
> the WorksheetFunction property, or Application. + "FunctionName", e.g:
>
> Range("A1").Formula=1+2
> Range("A1").Formula=WorksheetFunction.Sum(1,2)
> Range("A1").Formula=Application.Sum(1,2)
>
> 2) you cant't use the worksheet reference format (Sheet!A1) in VBA unless 
> you enclose the formula in quotation marks, e.g.:
>
> Range("A1").Formula="=SUM(Grid!D3,Grid!H3,Grid!L3,...,Grid!CR3)"
>
> if you want to have a VBA formula you should do somethink like this:
>
> Range("A1").Formula=Application.Sum(Sheets("Grid").Range("D3"), _
> 
> Sheets("Grid").Range("H3"),Sheets("Grid").Range("L3"),...,Sheets("Grid").Range("CR3"))
>
> 3) finally, the use of '+' operator within the SUM function is redundant
>
> Regards,
> KL
>
>
> "Derek Norman" <Derek.Norman.1teaqa_1123419900.8495@excelforum-nospam.com> 
> wrote in message 
> news:Derek.Norman.1teaqa_1123419900.8495@excelforum-nospam.com...
>>
>> Hi
>>
>> I'm trying to sum a set of cells using
>> =SUM(Grid!D3+Grid!H3+Grid!L3...Grid!CR3) and I get an error message
>> saying 'A value used in the formula is of the wrong data type'. What
>> does this mean??
>>
>>
>> -- 
>> Derek Norman
>> ------------------------------------------------------------------------
>> Derek Norman's Profile: 
>> http://www.excelforum.com/member.php?action=getinfo&userid=26004
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=393659
>>
>
> 


0
8/7/2005 1:36:06 PM
Reply:

Similar Artilces:

leave out certain data in a query..
I have a query that pulls all of the Invoice #'s but I dont want it to pull Invoice # 1, 6651, and 2. How can I exclude these from the query? Thanks. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1 On Mon, 16 Apr 2007 16:12:34 GMT, stephendeloach via AccessMonster.com wrote: > I have a query that pulls all of the Invoice #'s but I dont want it to pull > Invoice # 1, 6651, and 2. How can I exclude these from the query? Thanks. As Criteria on the Invoice# column, write (If the Invoice# field is a Number datatype):...

Data validation #17
I have data validation set up on a cell but I want the same validation to be performed on all the cells in the column. Is there a way of doing this without go into 60000 cells and setting it up manually Thanks Hi Copy the cell with the validation(ctrl-C) Select the cells where you want the same validation Right click on them and choose Paste Special Check Validation OK -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Newbie" <noidea@nospam.com> wrote in message news:OXgQ0kK8DHA.3880@tk2msftngp13.phx.gbl... > I have data validation set up...

Accessing historical payroll data after PR module has been deactiv
I have a customer that is going to de-activate their GP Payroll module to use an outside Payroll service provider, but they still want to access their historical Payroll data. If they go thru the process of of formally de-activating their Payroll module, and are issued new registration keys that don't include Payroll, is there a way they can see their historical payroll data past 1 year using the Inquiry function? Is there a way to see the historical information using the Report Writer? Thanks, Mark Report Writer would probably work but you'd have to run the reports from with...

Averaging only cells with data
I have a column with 31 rows. In select cells, there is a number. I want to average the numbers. How do I average only the cells that contain data, ignoring blank cells? Thanks! Excel 2003 It will only average the cells with numbers. Make sure the blank cells ARE blank. -- Ken Russell kenrussellyourhat@optushome.com.au Remove yourhat to reply by e-mail .. "Randy Lefferts" <anonymous@discussions.microsoft.com> wrote in message news:16d501c4d6e7$46e0bee0$a501280a@phx.gbl... >I have a column with 31 rows. In select cells, there is > a number. I want to avera...

Data Validation Input message position
A workbook I've created has quite a few Data Validations. By accident I found out that when the input message is showing, you can use the mouse to shift it to wherever you want on the sheet. Once that is done, all other data validation messages will appear in that same spot (which is not always a bad thing) BUT..... The messages will no longer be shown in their default position (next to or near the active cell that causes the validation input to appear). Is there a way to restore that default setting? Rob AFAIK, once the input message has been moved, you can't reset it to its ori...

Data Migration Framework problems
Hi, Sorry to post this again, but I've been looking all over trying to solve this problem but no luck. I'm trying to migrate some data (Accounts, Contacts and Activities) to MS CRM 3.0 using the Data Migration Framework. I succeeded in migrating the Accounts and Contacts and just over 600 out of about 7000 activities (the activities are simple tasks associated with the accounts). I noticed that all the non-migrated activities have status set to "Completed". Moreover, when I change the status of those records to "In Progress" (just to test) some of them succeed. Doe...

User entered data in XML
I'm writing data the user enters out to an XML file (using DataSet.WriteXML; DataSet is a typed DataSet created from an XML Schema). The problem I'm having is the user can (and needs to) enter characters like &, < and >. Is there some .NET function (maybe something in System.Xml) that will take a string and format it for output to an xml file (such as change all &'s to &amp;, etc.)? I'd hate to have to do String.Replace("&", "&amp;").Replace(...).Replace(...) all the fields manually, but if that's my only alternative... Ne...

Graph Source Data
I have a cell A1 which consists of value : 'Data'!$A$1:$B$4 I want to create a graph based on that cell. Can I use indirect for this purpose ? Thanks Just highlight the range and name it mygraphrng or something then goto the graph wizard and where it asks for source data type in sheet12!mygraphrng modify to suit -- Don Guillett SalesAid Software donaldb@281.com "diego" <porno_abis@hotmail.com> wrote in message news:00c401c3b2b2$91237a10$a401280a@phx.gbl... > > I have a cell A1 which consists of value : 'Data'!$A$1:$B$4 > > I want to create...

Showing data that does not match Querie
Hi Guys/Girls this is my first time actually using Access fully but I would like to know if i can do one thing, I can import my 2 worksheets Emp1 and Emp2 the problem I have is I want to display a listing where if 2 queries match from emp1 to emp2. What it needs to do is this i know that there are entries that do not appear in emp2 and I want to see what does not appear. so basically to show data that does not match the Querie. Thanks in advance for any help. There is an unmatched query wizard that will build a query for you to show rows in table one that don't exist in table two....

Split combined date time data #3
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) One way: Data/Text to Columns. Select Delimited, and click Next. Check the Space checkbox and click Finish. In article <D84AFF1F-CA34-456B-AA06-926914397A3C@microsoft.com>,...

Using data val. drop-down list to select offset
I'm using MS Excel '02 w/SP3 to make a spreadsheet that automatically fills in certain fields based on a model number that is selected from a drop-down list created using the data validation method. What I would like to do is have a cell that reflects the index value of the item selected from the drop-down list. That index value is then used in an OFFSET formula that fills in the fields. As an example of what I want to do: I pick the second item in the drop down list; this stores the number 2 in cell "C1"; the formula =OFFSET($A$1,$C$1,1) is entered into "D1&quo...

Extracting Data from Word 2007 forms
I created a form for our agents to reduce the amount of paperwork they have to carry with them. I decided to use a form in Word 2007. Now they're wanting to be able to extract data from these forms to upload to a database. I am having trouble finding a good way to extract this data in office 2007. My question is what is the best way to go about doing this? I've seen discussions on how to do it from office 2003 but can't seem to use the same procedure in '07. Do I need to create a Macro, VBA code, or is there some simpler way to do it that I've overlooke...

Best way to access data outside MS-CRM
Hi, What's the best way to access from inside MS-CRM, data related to example stock information which is stored in another SQL Database. Import routines are not an options, as that has to be real-time. Thanks Taja, Create an ASP page to present the data. When you use the ISV.config to add the button to load the form, you can pass the ID of the current record. Stephen "Taja" <taja2004@hotmail.com> wrote in message news:ugIJujQwEHA.3288@TK2MSFTNGP14.phx.gbl... > Hi, > > What's the best way to access from inside MS-CRM, data related to example >...

How do I wrap text in the legend key of a data table in a graph?
Is it possible to wrap text in the legend key of a data table within a graph? I don't know what you can do in a chart's data table. I generally make a table in a range on the worksheet. In the worksheet you can pretty much format everything however you like, and include or exclude data regardless of whether it's even in the chart. If the chart is embedded in the worksheet, use a range beside or below the chart for your table. If the chart is on its own chart sheet, make your table in any worksheet, then use the camera tool to place a dynamic picture of the table in the chart...

Money 2006 MNY file type
I installed Money 2006 from built-in "administrator" account on Windows XP. When I login using another XP account, the file type association with Money 2006 program is not there. Thanks In microsoft.public.money, AG wrote: >I installed Money 2006 from built-in "administrator" account on Windows XP. >When I login using another XP account, the file type association with Money >2006 program is not there. If the other XP account is an administrator account, you can reinstall from there. If not, I am thinking that reinstalling from that built-in "admini...

TYPING KARAOKE PGS NEED HELP
i am trying to figure out how to type continuous pages for a karaoke catalog 3 headings song title artist disk# when i check print preview it only shows me one page i am new at this typing one handed due to a stroke but i need to also be able to add new songs and then sort them alpa then need to redo by artist the song title then disk if any one can help it would be most appreciated there is approximately 192 pgs ea book thank you so very much patti In Print preview press PgDn. Does this give he next pages? Another issue might be print area. Go to File-Print Area-Clear Print A...

"xsi:type" missing from serialization of object- XmlSerializer
Hi, I'm serializing an object using XmlSerializer. It is serializing, but we are getting errors upon deserialization. We use the following code to serialize: FileStream fs = new FileStream(NavCmdFile, FileMode.Create, FileAccess.Write, FileShare.None); XmlSerializer xmlFmt = new XmlSerializer(someObject.GetType()); xmlFmt.Serialize(fs, someObject); One of the objects is a parameter and this is what you get: <someObject_parameter> <name>name</name> <source>soucename</source> <value type="xsd:int">1</value> </someObject_param...

How do I view all data in a cell regardless of number of characte.
Click in the cell. Look at the Formula Bar! -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "DavidMarquez" <DavidMarquez@discussions.microsoft.com> wrote in message news:A29A9CA8-57E4-403F-B3F7-6176308CE8FE@microsoft.com... > Also, if you mean how much cell content you can display on the sheet, itself, however, that's a different story. A cell can display up to 255 characters wide (column width) by 409 points tall (row height). Font s...

how can i transfer data from one sheet to another without blank li
Dear All I have extracted data from a system to a sheet including blank lines between the data. Any idea of how i can transfer the data from one sheet to another without any blank lines, in order to do my job using excel functions? Thanks in advance If you mean blank rows why dont you just sort the data? In case you need to retain the order (you can use a temporary column); insert an additional column say A. Number 1,2,....till the end in cells a1,a2 ...... Sort col B. Delete the blank rows..and then sort by ColA...Remove ColA -- Jacob (MVP - Excel) "Manos&q...

Hovering Over the data point shows different X-Axis
I have an area chart with several data points. The chart looks correct, but if I hover over one of the data points the X-axis that it indicates is one off the correct X axis. If I only select data for 2 years (24 points) it is okay. As soon as I add the 25th, the hover over shows the following month (the data is still correct). Is this a limitation in Excel or has anyone else come across this? ...

I've put data to columns - counted
I've got a two columns spreadsheet with cells which have more than one set of data in them. I've put column A from text to columns and managed to count it, but now need to find out how to add the filter button at the top of the column which enables me to filter according to number of names i want to see. It is a name and address spreadsheet but the names and addresses are mixed up, with some cells containing more than 5 names (column a) and some more than 5 addresses (column b). i've sorted column a, but cannot find a unique separator for column b. please help me!!! ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

Excel (2007) Chart Refuses to Accept More Data Points
I have a simple chart set up that references data points which are vertically ten spaces apart. The chart has worked fine for months. However, when I went to update it with another series point today it simply will not "OK" the extra point. I click enter, I click okay, and NOTHING. It's not just one chart that this is happening with, either. In the same file I have another chart that references another point that is spaced like the previous one but I have the same problem. I've never had this happen before and tooling around with it hasn't yielded any fruit. Some sa...

Serializing array of userdefined types
Hello, I want to have a class that contains only a collection of another class. For example: public __gc class Alignment { public: Alignment(); ... }; // Probably using XmlIncludeAttribute such as this is wrong... [System::Xml::Serialization::XmlIncludeAttribute(__typeof(Alignment))] public __gc class Alignments : public System::Collections::ArrayList { public: Alignments(); ... }; I figured I could use Alignments like this: Alignment* alignment = new Alignment; Alignments* alignments = new Alignments; alignments->Add(alignment); BTW, I am using XmlSerializer and XmlTextWriter....

Different error bar values within a data series?
I am trying to put error bars into my graph but need the values to be different. However, if I change one point it changes all of the points? Hi, Create two columns, one for '+' errors and the other for '-' errors. Rightclick on any data-point on your graph, "Format Data Series" --> select the appropriate "Error Bars" Tab --> under Error Amount check "Custom" and enter the ranges for the '+' and '-' values --> "OK" Regards, B. R. Ramachandran "Izzy" wrote: > I am trying to put error bars ...