Code này tại sao lại không xóa được các khoảng trắng trong các ô bảng tính

[Dauthivan]

Không hiểu dữ liệu cơ quan em tại file em đính kèm bài http://www.giaiphapexcel.com/forum/...số-ký-tự-chuyển-Text-sang-Value-cả-bảng-nhanh được kết xuất từ chương trình Kế toán sang Excel kiểu gì? Mà tại tất cả các Sheet, số liệu số hiện ra màn hình hai đầu nó có rất nhiều khoảng trắng khiến excel hiểu đó là Text không chịu tính toán

Em thử dùng Code sau để xóa, nhưng chạy nó không xóa được, xin hãy sửa giúp em:

Sub Trim()
For Each mycell In Selection
  mycell.Value = Trim(mycell.Value)
    Next
End Sub

 

[ptm0412]

Sao không đọc bài số 4 bên topic đó?

 

[Dauthivan]

Cảm ơn thày, do vôi quá em không kịp để ý. Em xin hỏi thêm nếu trường hợp này viết bằng VBA thì sẽ phải sửa thế nào ah?

 

[ptm0412]

Làm 2 dòng lệnh thay thế (replace):

Mycell.value = Replace(Mycell.Value, ".","")
Mycell.value = Replace(Mycell.Value, chr(10),"")

Hoặc gộp (lồng) 2 hàm Replace như lồng 2 hàm Substitute của Excel

 

[Dauthivan]

Em làm được rồi ah, nhưng em xin được hỏi thành phần chr(10) là gì thế. Kính mong thày chỉ dạy

Sub LoaiDau()
For Each Mycell In Selection
 Mycell.Value = Replace(Mycell.Value, ".", "")
Mycell.Value = Replace(Mycell.Value, Chr(10), "")
    Next
End Sub

 

[nghiaphuc]

Cảm ơn thày, do vôi quá em không kịp để ý. Em xin hỏi thêm nếu trường hợp này viết bằng VBA thì sẽ phải sửa thế nào ah?

Dựa theo bài #4 bên kia thì ta có code này:

Sub Test()
    With Sheets("Loc").Range("A5:I47")
        .Replace ".", ""
        .Replace Chr(10), ""
    End With
End Sub

 

[mhung12005]

Em làm được rồi ah, nhưng em xin được hỏi thành phần chr(10) là gì thế. Kính mong thày chỉ dạy

Sub LoaiDau()
For Each Mycell In Selection
 Mycell.Value = Replace(Mycell.Value, ".", "")
Mycell.Value = Replace(Mycell.Value, Chr(10), "")
    Next
End Sub

Cái này mình không nhầm thì nó là ký tự char() trong excel. Chr(10) = char(10) = khoảng trắng.

 

[viehoai]

Em làm được rồi ah, nhưng em xin được hỏi thành phần chr(10) là gì thế. Kính mong thày chỉ dạy

Sub LoaiDau()
For Each Mycell In Selection
 Mycell.Value = Replace(Mycell.Value, ".", "")
Mycell.Value = Replace(Mycell.Value, Chr(10), "")
    Next
End Sub

Chr(10) là ký tự tương đương bạn nhấn Alt+Enter trong cell (ký tự xuống hàng)

 

[ptm0412]

Trong bài 4 bên kia, nmhung đã nói rõ thế này:
Đếm thấy 14 ký tự, nhưng dùng len thì thấy 15, nghĩa là có 1 ký tự lạ
Dùng Right 1 ký tự sau đó dùng hàm code thì thấy là 10:
=Code(Right(B5)) = 10

Vậy đó là ký tự có code 10

Muốn có ký tự code 10, trong Excel thì nhấn Alt và gõ 0010 bằng phím số Numpad, hoặc dùng hàm CHAR(10), trong VBA thì là hàm Chr(10)

 

[Dauthivan]

Chẳng là mỗi file của em có khoảng 15 Sheet, nên nếu em chọn từng vùng một thì sẽ rất lâu. Em đã dùng cách chọn tất cả các Sheet (15 Sheet), sau đó em bấm Ctrl+A để lựa chọn toàn bộ dữ liệu tất cả các Sheet nhưng khi chạy Code thấy rất lâu (thậm chí là treo máy)

Các bảng của em có cấu trúc như nhau, vùng dữ liệu trong mỗi Sheet đều là A1:J50. Em muốn bổ sung thao tác lựa chọn toàn bộ vùng A1:J50 cho tất cả các Sheet thì em phải viết thêm vào Code trên thế nào ah?

 

[Hai Lúa Miền Tây]

Chẳng là mỗi file của em có khoảng 15 Sheet, nên nếu em chọn từng vùng một thì sẽ rất lâu. Em đã dùng cách chọn tất cả các Sheet (15 Sheet), sau đó em bấm Ctrl+A để lựa chọn toàn bộ dữ liệu tất cả các Sheet nhưng khi chạy Code thấy rất lâu (thậm chí là treo máy) Các bảng của em có cấu trúc như nhau, vùng dữ liệu trong mỗi Sheet đều là A1:J50. Em muốn bổ sung thao tác lựa chọn toàn bộ vùng A1:J50 cho tất cả các Sheet thì em phải viết thêm vào Code trên thế nào ah?

Viết thêm 1 vòng lặp duyệt qua các sheet đó là được.
Ví dụ

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), ""
    End With
Next
End Sub

 

[Dauthivan]

Viết thêm 1 vòng lặp duyệt qua các sheet đó là được.
Ví dụ

[PHP]Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), ""
    End With
Next
End Sub[/PHP]

[/QUOTE]

Code này chưa được vì nó không những xóa ký tự Chr(10) ở cuối ô mà nó còn xóa luôn ký tự Chr(10) ở giữa ô dẫn tới sau khi chạy Code sẽ gặp trường hợp trong một ô :
Trước khi chạy Code: Tên tổ chức
                              [COLOR=#ffffff]---------------------[/COLOR]huy động
Sau khi chạy Code thì sẽ là [COLOR=#ff0000][B]Tên tổ chứchuy động[/B][/COLOR]

Em định sửa Code thành 
[PHP]Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), " "
    End With
Next
End Sub[/PHP]
tiếp theo đó em dùng hàm trim cắt khoảng trắng ở đầu đi, nhưng em [B]chưa biết cách thêm hàm Trim trong VBA thế nào[/B]? Ai biết giúp em với.


[COLOR=#0000ff]Em định thêm 1 số tính năng như thêm Font chữ, cỡ chữ vào code (nhưng không biết nguyên nhân tại sao không chạy ah)?[/COLOR]

[PHP]Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), ""
        .Name = ".VnTime"
        .Size = 9
        End With
Next
End Sub[/PHP]

 

[nghiaphuc]

Code này chưa được vì nó không những xóa ký tự Chr(10) ở cuối ô mà nó còn xóa luôn ký tự Chr(10) ở giữa ô dẫn tới sau khi chạy Code sẽ gặp trường hợp trong một ô :
Trước khi chạy Code: Tên tổ chức
---------------------huy động
Sau khi chạy Code thì sẽ là Tên tổ chứchuy động
Em định sửa Code thành

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), " "
    End With
Next
End Sub

tiếp theo đó em dùng hàm trim cắt khoảng trắng ở đầu đi, nhưng em chưa biết cách thêm hàm Trim trong VBA thế nào? Ai biết giúp em với.
Em định thêm 1 số tính năng như thêm Font chữ, cỡ chữ vào code (nhưng không biết nguyên nhân tại sao không chạy ah)?

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    With Sheets(ws.Name).Range("A1:J50")
        .Replace ".", ""
        .Replace Chr(10), ""
        .Name = ".VnTime"
        .Size = 9
        End With
Next
End Sub

1. Trong VBA không có hàm TRIM có tác dụng cắt khoảng trắng ở giữa như trên Worksheet. Để khắc phục vấn đề này, bạn có thể Replace thêm một lần nữa (2 khoảng trắng --> 1 khoảng trắng): .Replace " ", " "
Hoặc cũng có thể sử dụng hàm TRIM của WorksheetFunction: WorksheetFunction.Trim
2. Định dạng Font: Sửa .Name = ".VnTime" thành .Font.Name = ".VnTime"

 

[Dauthivan]

Riêng vùng A1 đến O3 em định dạng nó là Vn.timeH, em thêm thế này thì Cú pháp sai ở đâu?

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
        With Sheets(ws.Name).Range("A1:O50")
        .Replace ".", ""
        .Replace Chr(10), " "
        .Replace "  ", " "
        .Font.Name = ".VnTime"
        .Font.Size = 9
        End With
           Next
           
           With Sheets(ws.Name).Range("A1:O3")
                .Font.Name = ".VnTimeH"
               End With
           Next
     End Sub

 

[Hoàng Trọng Nghĩa]

Riêng vùng A1 đến O3 em định dạng nó là Vn.timeH, em thêm thế này thì Cú pháp sai ở đâu?

Sub Test()
Dim ws As Worksheet
[B][COLOR=#0000cd]For [/COLOR][/B]Each ws In ThisWorkbook.Sheets
        With Sheets(ws.Name).Range("A1:O50")
        .Replace ".", ""
        .Replace Chr(10), " "
        .Replace "  ", " "
        .Font.Name = ".VnTime"
        .Font.Size = 9
        End With
           [COLOR=#ff0000][B]Next[/B][/COLOR]
           
           With Sheets(ws.Name).Range("A1:O3")
                .Font.Name = ".VnTimeH"
               End With
           [COLOR=#0000cd][B]Next[/B][/COLOR]
     End Sub

Bạn thừa 1 từ Next, có lẽ bạn nên bỏ từ Next màu đỏ.

 

[Dauthivan]

Cảm ơn bác Minh thiện, em đã làm được cái đó rồi. Nhưng em muốn thêm 1 công đoạn nữa bằng việc thay dấu phảy (,) bằng dấu chấm (.) thì chạy nó không thay thế, Code của em:

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
        With Sheets(ws.Name).Range("A1:O50")
        .Replace Chr(10), " "
        .Replace "  ", " "
        .Font.Name = ".VnTime"
        .Font.Size = 9
        .Replace ",", "."
                End With
           With Sheets(ws.Name).Range("A1:O3")
                .Font.Name = ".VnTimeH"
               End With
           Next
     End Sub

Không hiểu cái đoạn .Replace ",", "." này của em có bị sai không?

(Sở dĩ em phải làm động tác này là chương trình của em sau khi kết xuất dấu , và dấu (.) lẫn lộn)

 

[nghiaphuc]

Cảm ơn bác Minh thiện, em đã làm được cái đó rồi. Nhưng em muốn thêm 1 công đoạn nữa bằng việc thay dấu phảy (,) bằng dấu chấm (.) thì chạy nó không thay thế, Code của em:

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
        With Sheets(ws.Name).Range("A1:O50")
        .Replace Chr(10), " "
        .Replace "  ", " "
        .Font.Name = ".VnTime"
        .Font.Size = 9
        .Replace ",", "."
                End With
           With Sheets(ws.Name).Range("A1:O3")
                .Font.Name = ".VnTimeH"
               End With
           Next
     End Sub

Không hiểu cái đoạn .Replace ",", "." này của em có bị sai không?
(Sở dĩ em phải làm động tác này là chương trình của em sau khi kết xuất dấu , và dấu (.) lẫn lộn)

1. Trước hết, góp ý với bạn một xíu (đây là ý cá nhân của mình và mình thường làm như vậy):
- Để tránh gặp lỗi như trên vừa đề cập (thừa, thiếu các dòng cùng cặp: For-Next, With-End With,...), bạn nên gõ cặp từ khóa này trước rồi mới Enter để chèn các dòng lệnh phía trong vào.
- Khi soạn code, bạn nên sử dụng phím Tab để phân cấp các dòng lệnh. Đây là một thói quen tốt, sau này dễ kiểm soát lỗi soạn thảo, chẳng hạn code trên, bạn trình bày như vầy sẽ dễ nhìn hơn:

Sub Test()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        With Sheets(ws.Name).Range("A1:O50")
            .Replace Chr(10), " "
            .Replace "  ", " "
            .Font.Name = ".VnTime"
            .Font.Size = 9
            .Replace ",", "."
        End With
        Sheets(ws.Name).Range("A1:O3").Font.Name = ".VnTimeH"
    Next
End Sub

Câu lệnh With phía sau không cần thiết vì trong đó chỉ có 1 lệnh đơn, nên đưa câu lệnh này ra cho gọn.
2. Về vấn đề thay thế dấu (,) bởi dấu (.) thì mình thấy vẫn thay thế bình thường, có lẽ việc bạn nhìn thấy trên bảng tính là do định dạng ô (Format Cells). Tuy nhiên, mình cũng đang thắc mắc tại sao không thay thế bởi ký tự rỗng ("") mà thay bởi dấu (.)? Và việc thay thế này cũng phải cẩn thận vì có thể liên quan đến vấn đề dấu thập phân với dấu phân cách hàng ngàn đấy.

 

[Dauthivan]

2. Về vấn đề thay thế dấu (,) bởi dấu (.) thì mình thấy vẫn thay thế bình thường, có lẽ việc bạn nhìn thấy trên bảng tính là do định dạng ô (Format Cells). Tuy nhiên, mình cũng đang thắc mắc tại sao không thay thế bởi ký tự rỗng ("") mà thay bởi dấu (.)? Và việc thay thế này cũng phải cẩn thận vì có thể liên quan đến vấn đề dấu thập phân với dấu phân cách hàng ngàn đấy.

Vâng, vụ này rắc rối lắm bác ah, do chương trình kết xuất lẫn lộn dấu phảy và dấu chấm lẫn lộn

Bản chất của dữ liệu đúng là chỉ có cột lãi suất (cột H) là dấu phảy (,) thôi, còn các số liệu còn lại trong bảng có đặc điểm đều là số nguyên dương (không có phảy đằng sau).

Em đang loay hoay tìm cách xử lý nó.

Tiện em gửi File kết xuất lên nhờ mọi người tư vấn giúp.

Em thử thay dấu phảy (,) bằng dấu chấm (.) bằng Code trên nhưng không có tác dụng, ví dụ ô H8 là 2,4 sau khi chạy code nó vẫn trơ trơ ra là 2,4.

(Hằng ngày em phải làm việc với khoảng 20 bảng như thế bác ah, thế mới mệt chứ)

 

[nghiaphuc]

Tiện em gửi File kết xuất lên nhờ mọi người tư vấn giúp.
Em thử thay dấu phảy (,) bằng dấu chấm (.) bằng Code trên nhưng không có tác dụng, ví dụ ô H8 là 2,4 sau khi chạy code nó vẫn trơ trơ ra là 2,4.
(Hằng ngày em phải làm việc với khoảng 20 bảng như thế bác ah, thế mới mệt chứ)

Cái dấu phẩy ở cột H là do Control Panel trong máy bạn quy định chứ không phải là do việc xuất dữ liệu, bởi vì mình mở trên máy mình thì chỗ này là 2.4 chứ không phải là 2,4 --> việc thay thế không có tác dụng là phải rồi.

 

[Dauthivan]

Cái dấu phẩy ở cột H là do Control Panel trong máy bạn quy định chứ không phải là do việc xuất dữ liệu, bởi vì mình mở trên máy mình thì chỗ này là 2.4 chứ không phải là 2,4 --> việc thay thế không có tác dụng là phải rồi.

Như vậy ngoài cột H lãi suất như bác nói, còn lại toàn bộ các ô như (ô L35 chẳng hạn) bây giờ Code phải viết thế nào để nó hiểu là 281.582 (Hai trăm tám mốt ngàn, năm trăm tám hai đồng) thay vì là 281,582.

Xin bày giúp em cách sửa hàng loạt, sửa thủ công sẽ rất lâu vì Khối lượng công việc của em rất nhiều.