VBA code to paste text as number

Question

Hello all,

I am using a macro to copy data from a web query and add it to another sheet.

My excel uses “,” as decimal separator.

My problem is that the numbers from the web query use “.” as decimal separator. Thus excel recognizes this numbers as text.

I copy, paste, replace “,” with “.” and multiply it by 1 to have it as a number.

The copy, paste, replace “,” with “.” is working fine.

 Excel is still considering this data as text after the multiplication.

Cany ou tell me who to solve this situation.

Here’s the code.

Dim LinhaIniOri, LinhaFinOri, LinhaIniDes, LinhaFinDes As Integer

    LinhaIniOri = 21

    LinhaFinOri = Sheets(“WebQ”).Range("a100000").End(xlUp).Offset(-1, 0).Row

    LinhaIniDes = Sheets(“Historic”).Range("b100000").End(xlUp).Offset(1, 0).Row

    LinhaFinDes = LinhaIniDes + (LinhaFinOri - LinhaIniOri)

    'Paste

    Sheets(“WebQ”).Range("G" & LinhaIniOri & ":G" & LinhaFinOri).UnMerge

     Sheets(“Historic”).Range("b" & LinhaIniDes & ":b" & LinhaFinDes) = Sheets(“WebQ”).Range("G" & LinhaIniOri & ":G" & LinhaFinOri).Value

    'Format

    With Sheets(“Historic”).Range("b" & LinhaIniDes & ":b" & LinhaFinDes)

        .Replace What:=",", Replacement:=""

        .Replace What:=".", Replacement:=","

    End With

‘Copy, multiply  “1”   

Sheets(“Historic”).Range("m2").Copy

    Sheets(“Historic”).Range("b" & LinhaIniDes & ":b" & LinhaFinDes).PasteSpecial , Paste:=xlPasteValues, Operation:=xlMultiply

Answer 1

Jim already pointed you to a great resource. I wanted to point you at a small programming error.

Despite what you might think, in this declaration:

Dim LinhaIniOri, LinhaFinOri, LinhaIniDes, LinhaFinDes As Integer

ONLY the last variable is declared as integer. The other ones are of type Variant.

In VBA you have to add the type definition to each one:

Dim LinhaIniOri As Integer, LinhaFinOri As Integer, LinhaIniDes As Integer, LinhaFinDes As Integer

For readabilities' sake I personally prefer:

Dim LinhaIniOri As Integer

Dim LinhaFinOri As Integer

Dim LinhaIniDes As Integer

Dim LinhaFinDes As Integer

Answer 2

Re:  converting numbers to text

See "Convert Currency With Different Separators" at...

    http://www.contextures.com/xlDataEntry03.html#PasteSpecial

You could record a macro while doing the above if you require VBA.

The raw data probably has text characters in it, so other VBA steps you could take ...

On the destination sheet before converting...

  Change the cell number format to a number format.

  Use the worksheet Trim function.

  If that is not completely successful then also use the worksheet Clean function

  If that is not completely successful then also replace Chr(160) with vbnullstring.

     Chr(160) is a non-breaking space and cannot be "trimmed".

'---

Jim Cone

Portland, Oregon USA

free & commercial excel programs  (n/a xl2013+)

http://jmp.sh/K95N3ee