Hi

I use the following code to sum the values of all numbers that appear in
column F:

Set Rng = Range("F1").End(xlDown)(2, 1)
Rng.Formula = "=SUM(F1:" & Rng(0, 1).Address(False, False) & ")"

The value that is returned is always 0 even though this is not the figure
that should be appearing. I use a similar piece of code and it works without
problem.

My first thought was that there may be a problem with the formatting of the
numbers, however, when I do a basic summing of two individual cells that
appear in that column (F100+F101) the formula works without problem.

Any ideas as to what might be the problem ?

Thanks
Sean

Hi
what does the manual entered formula
=SUM(F100:F101)
return?
I would assume these values are stored as 'Text'. The formula
=F100+F101
converts these values automatically to real number but SUM does not do
this

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean" schrieb im Newsbeitrag
...
Hi

I use the following code to sum the values of all numbers that appear
in
column F:

Set Rng = Range("F1").End(xlDown)(2, 1)
Rng.Formula = "=SUM(F1:" & Rng(0, 1).Address(False, False) & ")"

The value that is returned is always 0 even though this is not the
figure
that should be appearing. I use a similar piece of code and it works
without
problem.

My first thought was that there may be a problem with the formatting
of the
numbers, however, when I do a basic summing of two individual cells
that
appear in that column (F100+F101) the formula works without problem.

Any ideas as to what might be the problem ?

Thanks
Sean

Thanks for your response Frank. Sum(F100:101) returns 0 whilst F100+F101
returns the correct value (1050.00).

Is there any way of formatting a column to real numbers ? If so, is it
possible to do it via a piece of code ?

Thanks
Sean

"Frank Kabel" wrote:

Hi
what does the manual entered formula
=SUM(F100:F101)
return?
I would assume these values are stored as 'Text'. The formula
=F100+F101
converts these values automatically to real number but SUM does not do
this

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean" schrieb im Newsbeitrag
...
Hi

I use the following code to sum the values of all numbers that appear
in
column F:

Set Rng = Range("F1").End(xlDown)(2, 1)
Rng.Formula = "=SUM(F1:" & Rng(0, 1).Address(False, False) & ")"

The value that is returned is always 0 even though this is not the
figure
that should be appearing. I use a similar piece of code and it works
without
problem.

My first thought was that there may be a problem with the formatting
of the
numbers, however, when I do a basic summing of two individual cells
that
appear in that column (F100+F101) the formula works without problem.

Any ideas as to what might be the problem ?

Thanks
Sean

Hi
try the following macro (processes the current selection)

sub convert_it
with selection
.value=.value
end with
end sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean" schrieb im Newsbeitrag
...
Thanks for your response Frank. Sum(F100:101) returns 0 whilst
F100+F101
returns the correct value (1050.00).

Is there any way of formatting a column to real numbers ? If so, is
it
possible to do it via a piece of code ?

Thanks
Sean

"Frank Kabel" wrote:

Hi
what does the manual entered formula
=SUM(F100:F101)
return?
I would assume these values are stored as 'Text'. The formula
=F100+F101
converts these values automatically to real number but SUM does not
do
this

--
Regards
Frank Kabel
Frankfurt, Germany

"Sean" schrieb im Newsbeitrag
...
Hi

I use the following code to sum the values of all numbers that
appear
in
column F:

Set Rng = Range("F1").End(xlDown)(2, 1)
Rng.Formula = "=SUM(F1:" & Rng(0, 1).Address(False, False) & ")"

The value that is returned is always 0 even though this is not
the
figure
that should be appearing. I use a similar piece of code and it
works
without
problem.

My first thought was that there may be a problem with the
formatting
of the
numbers, however, when I do a basic summing of two individual
cells
that
appear in that column (F100+F101) the formula works without
problem.

Any ideas as to what might be the problem ?

Thanks
Sean