Summ Number with dot and com

[sal21]

why the summ not is correct!!!

[HansV]

If you set the horizontal alignment of E1:E4 to General, you will see that some values are left-aligned. This means that Excel sees them as text.

S2569.png

Select E1, press F2 to edit, then press Enter. You'll see that the value will now be right-aligned, and that the sum changes.
Repeat for E2 and E3.

S2570.png

[sal21]

Tks Bro.
Possibile to do via code vb6?

[p45cal]

In the attached, table at cell G1:
Date in first column translated from text to Excel date
Column C split using valore: then:
- last comma removed if there is one.
- decimal number converted from Italian format number to plain number.

If you should change/add/delete the test data in the left table, right-click the new table and choose refresh to update it.
You seem to have Excel 2016 at least, so this should work for you.

2024-06-25_113542.jpg

[sal21]

If you set the horizontal alignment of E1:E4 to General, you will see that some values are left-aligned. This means that Excel sees them as text.


S2569.png


Select E1, press F2 to edit, then press Enter. You'll see that the value will now be right-aligned, and that the sum changes.
Repeat for E2 and E3.


S2570.png

Tks Bro.
Possibile to do via code vb6?

[HansV]

Code: Select all

Sub Text2Num()
    Application.DisplayAlerts = False
    Range("C1:C4").TextToColumns _
        Destination:=Range("D1"), _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
        Other:=True, OtherChar:=":"
    Application.DisplayAlerts = True
End Sub

[HansV]

Even shorter:

Code: Select all

Sub Text2Num()
    Range("E1:E4").TextToColumns
End Sub

[sal21]

Even shorter:

Code: Select all

Sub Text2Num()
    Range("E1:E4").TextToColumns
End Sub

Bro can you integrate your tips with the code above? (is a very very old code)

Code: Select all

Option Explicit

Dim XlPApp As New Excel.Application
Dim XlPWbk As Excel.Workbook
Dim XlPWsh As Excel.Worksheet
Dim FileXLS As String
Private Sub Command1_Click()

  Dim X

  FileXLS = "C:\VB6-Excel\Test.xls"
   
  Set XlPWbk = XlPApp.Workbooks.Add ' Apro il foglio di excel
      
    Set XlPWsh = XlPWbk.Worksheets.Add:  XlPWsh.Name = "Foglio di test" ' Nuovo sheet
    XlPWsh.Columns(1).ColumnWidth = 10: XlPWsh.Columns(1).NumberFormat = "dd/mm/yyyy" ' Data
    XlPWsh.Columns(2).ColumnWidth = 10: XlPWsh.Columns(2).NumberFormat = "@"          ' Nome
    XlPWsh.Columns(3).ColumnWidth = 40: XlPWsh.Columns(3).NumberFormat = "@"          ' Descrizione
    XlPWsh.Columns(4).ColumnWidth = 10: XlPWsh.Columns(4).NumberFormat = "General"   ' Entrate
    XlPWsh.Columns(5).ColumnWidth = 10: XlPWsh.Columns(5).NumberFormat = "#,##0.00"   ' Uscite
    XlPWsh.Columns(6).ColumnWidth = 10: XlPWsh.Columns(6).NumberFormat = "General"   ' Saldo
      
    X = CellaExcelP(1, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(1, 5, "R", ConvertiNumero(Format(1027.51, "#,##0.00")))
    X = CellaExcelP(1, 3, "L", "Prima riga di prova, valore: 1.027,51")
      
    X = CellaExcelP(2, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(2, 5, "R", Format(38745.13, "#,##0.00"))
    X = CellaExcelP(2, 3, "L", "Seconda riga di prova, valore: 38.745,13")

    X = CellaExcelP(3, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(3, 5, "R", ConvertiNumero(938.1))
    X = CellaExcelP(3, 3, "L", "Terza riga di prova, valore: 938,10")

    X = CellaExcelP(4, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(4, 5, "R", ConvertiNumero(7048))
    X = CellaExcelP(4, 3, "L", "Quarta riga di prova, valore: 7.048,00")

    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.ActiveWorkbook.SaveAs FileXLS
  
  XlPWbk.Close False
  XlPApp.Quit
  
  X = MsgBox("Elaborazione terminata", vbOKOnly, "Stampa bollettini")

End Sub
Private Function ConvertiNumero(Prima As String) As Double

  Prima = Replace(Prima, ".", "")
  ConvertiNumero = CDbl(Replace(Prima, ".", ","))

End Function
Private Function CellaExcelP(X As Integer, Y As Byte, Allinea As String, Valore As String) ' Stampa riga sui partitari
  
  XlPWsh.Cells(X, Y).RowHeight = 15
  XlPWsh.Cells(X, Y).Font.Size = 11
  XlPWsh.Cells(X, Y).Font.Name = "Calibri"
  If Y = 1 Then
    XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 6)).Borders.LineStyle = xlContinuous
    XlPWsh.Cells(X, Y) = Valore
  ElseIf Y = 3 Then
    If Left$(Valore, 6) = "Canone" Then
      XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 35
    ElseIf Left$(Valore, 6) = "Accont" Or Left$(Valore, 6) = "Consun" Then
      XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 19
    ElseIf Left$(Valore, 6) = "Contri" Then
      XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 44
    ElseIf Left$(Valore, 6) = "Inc.Af" Then
      XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 2
    Else
      XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 20
    End If
    XlPWsh.Cells(X, Y) = Valore
  ElseIf Y = 5 Then
    XlPWsh.Cells(X, Y) = Valore
  '  XlPWsh.Cells(X, Y) = Format(Valore, "#,##0.00")
    If CDbl(Valore) < 0 Then XlPWsh.Cells(X, Y).Font.Color = vbRed Else XlPWsh.Cells(X, Y).Font.Color = vbBlack
  End If
    
  If Allinea = "R" Then XlPWsh.Cells(X, Y).HorizontalAlignment = xlRight Else If Allinea = "L" Then XlPWsh.Cells(X, Y).HorizontalAlignment = xlLeft Else XlPWsh.Cells(X, Y).HorizontalAlignment = xlCenter
  
End Function

[HansV]

Code: Select all

Option Explicit

Dim XlPApp As New Excel.Application
Dim XlPWbk As Excel.Workbook
Dim XlPWsh As Excel.Worksheet
Dim FileXLS As String

Private Sub Command1_Click()
    Dim X

    FileXLS = "C:\VB6-Excel\Test.xls"

    Set XlPWbk = XlPApp.Workbooks.Add    ' Apro il foglio di excel

    Set XlPWsh = XlPWbk.Worksheets.Add: XlPWsh.Name = "Foglio di test" ' Nuovo sheet
    XlPWsh.Columns(1).ColumnWidth = 10: XlPWsh.Columns(1).NumberFormat = "dd/mm/yyyy"   ' Data
    XlPWsh.Columns(2).ColumnWidth = 10: XlPWsh.Columns(2).NumberFormat = "@"         ' Nome
    XlPWsh.Columns(3).ColumnWidth = 40: XlPWsh.Columns(3).NumberFormat = "@"         ' Descrizione
    XlPWsh.Columns(4).ColumnWidth = 10: XlPWsh.Columns(4).NumberFormat = "General"   ' Entrate
    XlPWsh.Columns(5).ColumnWidth = 10: XlPWsh.Columns(5).NumberFormat = "#,##0.00"  ' Uscite
    XlPWsh.Columns(6).ColumnWidth = 10: XlPWsh.Columns(6).NumberFormat = "General"   ' Saldo

    X = CellaExcelP(1, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(1, 5, "R", ConvertiNumero(Format(1027.51, "#,##0.00")))
    X = CellaExcelP(1, 3, "L", "Prima riga di prova, valore: 1.027,51")

    X = CellaExcelP(2, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(2, 5, "R", Format(38745.13, "#,##0.00"))
    X = CellaExcelP(2, 3, "L", "Seconda riga di prova, valore: 38.745,13")

    X = CellaExcelP(3, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(3, 5, "R", ConvertiNumero(938.1))
    X = CellaExcelP(3, 3, "L", "Terza riga di prova, valore: 938,10")

    X = CellaExcelP(4, 1, "C", Format(Now, "dd/mm/yyyy"))
    X = CellaExcelP(4, 5, "R", ConvertiNumero(7048))
    X = CellaExcelP(4, 3, "L", "Quarta riga di prova, valore: 7.048,00")

    XlPWsh.Range("E1:E4").TextToColumns

    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.Worksheets(XlPApp.Worksheets.Count).Delete
    XlPApp.ActiveWorkbook.SaveAs FileXLS

    XlPWbk.Close False
    XlPApp.Quit

    X = MsgBox("Elaborazione terminata", vbOKOnly, "Stampa bollettini")
End Sub

Private Function ConvertiNumero(Prima As String) As Double
    Prima = Replace(Prima, ".", "")
    ConvertiNumero = CDbl(Replace(Prima, ".", ","))
End Function

Private Function CellaExcelP(X As Integer, Y As Byte, Allinea As String, Valore As String)    ' Stampa riga sui partitari
    XlPWsh.Cells(X, Y).RowHeight = 15
    XlPWsh.Cells(X, Y).Font.Size = 11
    XlPWsh.Cells(X, Y).Font.Name = "Calibri"
    If Y = 1 Then
        XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 6)).Borders.LineStyle = xlContinuous
        XlPWsh.Cells(X, Y) = Valore
    ElseIf Y = 3 Then
        If Left$(Valore, 6) = "Canone" Then
            XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 35
        ElseIf Left$(Valore, 6) = "Accont" Or Left$(Valore, 6) = "Consun" Then
            XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 19
        ElseIf Left$(Valore, 6) = "Contri" Then
            XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 44
        ElseIf Left$(Valore, 6) = "Inc.Af" Then
            XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 2
        Else
            XlPWsh.Range(XlPWsh.Cells(X, 1), XlPWsh.Cells(X, 5)).Interior.ColorIndex = 20
        End If
        XlPWsh.Cells(X, Y) = Valore
    ElseIf Y = 5 Then
        XlPWsh.Cells(X, Y) = Valore
        '  XlPWsh.Cells(X, Y) = Format(Valore, "#,##0.00")
        If CDbl(Valore) < 0 Then XlPWsh.Cells(X, Y).Font.Color = vbRed Else XlPWsh.Cells(X, Y).Font.Color = vbBlack
    End If

    Select Case Allinea
        Case "R"
            XlPWsh.Cells(X, Y).HorizontalAlignment = xlRight
        Case "L"
            XlPWsh.Cells(X, Y).HorizontalAlignment = xlLeft
        Case Else
            XlPWsh.Cells(X, Y).HorizontalAlignment = xlCenter
    End Select
End Function