convert text to number

[Guest]

I have a user interface which stores the inputted data onto a separate sheet
however when I come to pull the total amount off of the data sheet it wont
pick it up, the cell is asking me to Convert numbers stored as text to
number. I can get it to pick up if I manually change it so it converts to a
number but I want the macro to do this for me automatically, does anyone have
any ideas? For example if i do a basic =Sum(A1:A5) then it will not pick up
the text number however =sum(A1+A2+A3+A4+A5) will. Your help is appreciated

Kind regards

Rob Evans
07869214171

 

[Guest]

First - find out why you are storing numbers as text in the first place -
should be able to fix the problem there.

If not, the standard way to convert taxt back to numbers is to copy a cell
containing the number 1 and paste special / multiply (alt+e, s, m on the
keyboard) onto your cells containing numbers stored as text values.

So in code you would need something like:

Dim rng1 As Range 'cellcontaining the number 1
Dim rngText As Range 'range containg your text numbers

rng1.Copy
rngText.PasteSpecial Operation:=xlPasteSpecialOperationMultiply

 

[Dave Peterson]

One way:
=SUMPRODUCT(--(A1:A5))

Personally, I think I'd fix the range so that it's really numbers. You may
remember to fix this one formula, but I would be scared that I'd miss references
in other formulas.

I use this technique:
Select an empty cell
Edit|copy
Select the range to fix
Edit|paste special|check Add and values.