Fail to copy number into cell

[perco754]

Hi,

From my VBA code I import some data from several wotkbooks into one destination sheet.
All columns except 2 contain text, the other 2 contain integers and doubles.

To import I simply use copy -> paste special values but some of the cells that should have double values gets translated as text and some numbers transforms into long removing the comma separator (it's , in the Swedish local instead of .)
For example a number 7,7429345 ends up like 77429345 which will naturally produce a huge fault later on in the post processing.
Another cell 4,93 gets translated as text by Excel -> "4,93".

So how can ensure that my imported data is copied with the correct format?

BR,
Peer

 

[Firefly2012]

Is the issue simply the destination cells' formatting? In which case have you tried pasting formats too?

 

[perco754]

My GetData method, where I read and copy the data into the new book, returns a string. If I don't set the formatting explicitly on the sheet the data gets presented as sometimes text and sometimes numbers. I want to make sure that the data does not change and thus I set the format in the destinations cells all to text and later, after the copying is done, I want to change back to whatever data type is needed.

This code seems to work

Private Sub FixNumbers(ByRef s As Worksheet)
    Dim r As Range
    Dim c As Range
    
    Set r = s.Range(col + "P3")
    
    Set r = s.Range(r, r.End(xlDown))
    For Each c In r
        c.NumberFormat = "0"
        c.Value = CInt(c.Value)
    Next
    
    Set r = s.Range("S3")
    
    Set r = s.Range(r, r.End(xlDown))
    For Each c In r
        c.NumberFormat = "0.00"
        c.Value = CDec(c.Value)
    Next
    
End Sub

 

[Firefly2012]

Maybe you should consider copying the data from the source workbook instead (ie Range.Copy) as that would avoid unwanted datatype conversions.

 

[perco754]

That is exactly what I'm doing But for some reason some data gets transformed by some Excel-behind-the-scene-property?

 

[Firefly2012]

Can you show the code you are using?