Data types?

  • Thread starter Thread starter Derek Norman
  • Start date Start date
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??
 
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]...
 
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
 
Back
Top