Problems with summing

[Guest]

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

 

[Frank Kabel]

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

 

[Guest]

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]

Hi
try the following macro (processes the current selection)

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