Summ Number with dot and com

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Summ Number with dot and com

Post by sal21 »

why the summ not is correct!!!
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summ Number with dot and com

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Summ Number with dot and com

Post by sal21 »

Tks Bro.
Possibile to do via code vb6?

User avatar
p45cal
2StarLounger
Posts: 161
Joined: 11 Jun 2012, 20:37

Re: Summ Number with dot and com

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Summ Number with dot and com

Post by sal21 »

HansV wrote:
25 Jun 2024, 08:27
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?

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summ Number with dot and com

Post by 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
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summ Number with dot and com

Post by HansV »

Even shorter:

Code: Select all

Sub Text2Num()
    Range("E1:E4").TextToColumns
End Sub
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4440
Joined: 26 Apr 2010, 17:36

Re: Summ Number with dot and com

Post by sal21 »

HansV wrote:
25 Jun 2024, 15:05
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

User avatar
HansV
Administrator
Posts: 78991
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summ Number with dot and com

Post by 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
Best wishes,
Hans