Convert a column to a numbers that can be summed

[youbitto]

Hello
I have this code that import a csv file to a worksheet and replace in the column "O" the "." with "," so I cant sum that column
however after excuting the code that column seems to be in a text format or something when I select that column it gives me the number of selected cells

My request is to covert the column "O" to number that can be summed

PS : I have a french excel that why I replace "." with ","

Sub LoadCSV_FileToSheet()

Dim wsheet As Worksheet, file_mrf As String
Dim wsDestination             As Worksheet

Worksheets("listCreance").Range("A:P").Clear
Worksheets("listCreance").Range("Q5:Y99999").Clear

Set wsheet = ActiveWorkbook.Sheets("listCreance")

file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")



With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A4"))

.TextFileParseType = xlDelimited

.TextFileCommaDelimiter = True

.Refresh

End With

Columns("O").Replace What:=".", Replacement:=","


End Sub

 

[kvsrinivasamurthy]

Change this

Columns("O").Replace What:=".", Replacement:=","

as

Columns("G").NumberFormat = "0,00"
Columns("O").Replace What:=".", Replacement:=","

 

[youbitto]

Change this

Columns("O").Replace What:=".", Replacement:=","

as

Columns("G").NumberFormat = "0,00"
Columns("O").Replace What:=".", Replacement:=","

This didn't help

what I get is in Image "1.png"
What I want in Image "2.png"

 

[youbitto]

I found the solution
Thank you for your help

Sub LoadCSV_FileToSheet()

Dim wsheet As Worksheet, file_mrf As String
Dim wsDestination             As Worksheet

Worksheets("listCreance").Range("A:P").Clear
Worksheets("listCreance").Range("Q5:Y99999").Clear

Set wsheet = ActiveWorkbook.Sheets("listCreance")

file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")



With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A4"))

.TextFileParseType = xlDelimited

.TextFileCommaDelimiter = True

.Refresh

End With


Columns("O").Replace What:=".", Replacement:=","

'Converting text to Nubmber

Dim SelectR As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("listCreance")

LastRow = sht.Cells(sht.Rows.Count, "O").End(xlUp).Row

Set SelectR = ThisWorkbook.Sheets("listCreance").Range("O5:O" & LastRow)

SelectR.TextToColumns Destination:=Range("O5"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True


End Sub

 

[Alex Blakenburg]

I know you have it working now but rather than add the extra conversion steps give this a try:
(added lines in blue - Remove the code you currently have below the "End With")

With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A4"))

    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
 
    .TextFileDecimalSeparator = "."
    .TextFileThousandsSeparator = ","
 
    .Refresh

End With

' remove the code you had below here

 

[youbitto]

I know you have it working now but rather than add the extra conversion steps give this a try:
(added lines in blue - Remove the code you currently have below the "End With")

With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A4"))

    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
 
    .TextFileDecimalSeparator = "."
    .TextFileThousandsSeparator = ","
 
    .Refresh

End With

' remove the code you had below here

Oh Thank you very much, this indeed worked better and with less code

 

[Alex Blakenburg]

Thanks for letting me know. Glad that it worked for you.