Data types?

D

Derek Norman

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

KL

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" <[email protected]>
wrote in message
news:D[email protected]...
 
K

KL

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
 
Top