Prev: how to close Read Only file without prompt to save copy
Next: copy and paste data from different sheets
From: IronKalli on 6 May 2010 08:21
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.
From: Gary''s Student on 6 May 2010 08:33
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.
From: IronKalli on 6 May 2010 10:43
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
 | 
Pages: 1
Prev: how to close Read Only file without prompt to save copy
Next: copy and paste data from different sheets