use formula on external data range
 

Hi All,
I've got a problem with data I read from a database.

I wrote a VBA Sub in Excel 2007 with retrieves data via
ActiveSheet.QueryTables.Add() per OLEDB out of a database.

This works fine and puts the data in the range beginning on cell "A8"

Now my problem:
I have a formula '=SUM(A8:A10) in cell "B1".
This formula always comes out = 0, although there are values in the cells.

But if I edit cell "A8" without changing the value (just the alignment
changes) it is recognized and the formula shows = 1

What am I missing here ?

Thanks for any help,
Ralf.

use formula on external data range
 

Your problem is that the data retrieved is being treated as text rather than
a number. You can simulate this by putting '1 in some cell, copying the
cell, and paste/special/value into A1.

A1 will APPEAR to have a value, but its really only Text, that is
=SUM(A1:A1) will display 0


To fix this in VBA, something like:

Sub convertt()
Range("A1").Value = Range("A1").Value
End Sub

--
Gary''s Student - gsnu201002


"IronKalli" wrote:

Hi All,
I've got a problem with data I read from a database.

I wrote a VBA Sub in Excel 2007 with retrieves data via
ActiveSheet.QueryTables.Add() per OLEDB out of a database.

This works fine and puts the data in the range beginning on cell "A8"

Now my problem:
I have a formula '=SUM(A8:A10) in cell "B1".
This formula always comes out = 0, although there are values in the cells.

But if I edit cell "A8" without changing the value (just the alignment
changes) it is recognized and the formula shows = 1

What am I missing here ?

Thanks for any help,
Ralf.

use formula on external data range
 

Hello,
thanks for your answer. It helps.

But do you know of a way to 'convince' VBA to directly insert
the data formatted to number or general and not as text ?

Kind regards,
Ralf

use formula on external data range
 

Hello,
thanks for your answer. It helps.

But do you know of a way to 'convince' VBA to directly insert
the data formatted to number or general and not as text ?

Kind regards,
Ralf