number/text problem

[John]

Worsheet is composed of numbers in string (txt) format.

is there a way to sum columns etc without destroying the txt format?

also

Is there a sum function in vb? I can't find one.

like c=sum("a1:a9") where c is an integer or long

Alternately. Is there a way to turn of calculating in only particular cells?

thanks
John

 

[Bob Bridges]

In VB in general if you want to add up a bunch of numbers you have to loop
through them all. But in VBA/Excel (which is what I suppose you mean in this
case) you can either do it that way, or you can imitate the Worksheet
function by saying Application.WorksheetFunctions.Sum(RangeObject), where
RangeObject is some Range reference, for instance
ActiveSheet.Range("A943").

As for summing up text values that look like numbers, you're asking about
doing it with an Excel formula not a VBA program, right? And I suppose
you've already tried just plain SUM()? If that doesn't work, the way I'd do
it is set up a range of formulae =VALUE(RC[-11]) (I use R1C1 notation when
possible but you can do it in A1 too of course) and sum those. VALUE, if I
remember correctly, converts a text string to the number it represents.

 

[OssieMac]

Hi John,

Without converting the individual values of the text range to numeric with
the worksheet VALUE function (or Val function in VBA) , I don't think you can
sum text values.

On the other question

c = WorksheetFunction.Sum(Range("A1:A9"))

Many of the worksheet functions are available in VBA with WorksheetFunction.
When you type it in, after placing the dot at the end of WorksheetFunction
you should get a dropdown listing them.