Text to numbers

[opislak]

Hi all,

Problem: make vba turn numbers stored as text into numbers, after the cells have been formatted as standard.

(sorry I cannot upload a mini sheet, for company policy prohibits installing software. I give screen shots hereby).

In the screen shots, you will find:
- in column A: the percentages I get from another application. There, they are stored as text
- in column B: a simple sum, so you see the errors immediately

This is a piece of my code:

Sub txt2nbr()

    Cells.ClearFormats

    Set txt2nr = Range("A1:A12")
    txt2nr.TextToColumns DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

The problem occurs when the code is executed twice (or more) (that's how it goes in real life, and not always with new data, so it should be possible to execute more than once).
The code works fine, if "Cells.ClearFormats" is skipped. However, I need to clear the format of the cells first. Leaving this part out is not an option.

As I am a European, my Excel is set with a decimal comma, not a point.

What happens:
- at first run: everything seems OK (80%, left aligned is turned into 80%, right aligned)
- at second run: Cells.ClearFormats turns 80% into 0,8 (zero comma eight) --> result is OK
- but then the formula turns 0,8 (zero comma eight) into 0.8 (zero point eight) --> result is that everything but 100% (1) becomes text again.

I already tried Comma:=False and Comma:=True, but with the same result.
Thanks for any input!
Patrick.

 

[jasonb75]

I can't see anything in your screen captures, see if this fixes it.

Sub txt2nbr()
Dim txt2nr as Range
Set txt2nr = Range("A1:A12")
With txt2nr
    .ClearFormats
    Range("XFD1048576").Copy
    .PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
End With
Application.CutCopyMode = False
End Sub