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

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:[email protected]...
>
> 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...o&userid=26004
> View this thread: http://www.excelforum.com/showthread...hreadid=393659
>

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" <[email protected]> wrote in message
news:up6%[email protected]...
> 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:[email protected]...
>>
>> 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...o&userid=26004
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=393659
>>
>
>