Code Kiểm tra số liệu nhập vào

nationalfox · 28/8/10

Xin chào diễn đàn;

Rất mong được chỉ giáo cho tôi ở trường hợp sau:

- Sheet1: Có chứa danh sách, số liệu gốc (reference data) ở cột B chẳng hạn có danh sách (Dừa, xoài, ổi, mận, hồng xiêm, nho, chôm chôm...)
- Sheet2: Nhập dữ liệu liên quan đến danh sách ở sheet1 trên nhưng trong 01 ô chứa cùng lúc các loại trên (Ví dụ: Xoài, Ổi hoặc Mận, hồng xiêm, nho)

Vậy đoạn Code nào để kiểm tra (sau khi nhập xong vào ô dữ liệu ở sheet2) số liệu không nằm trong sheet1
(vd: Ổi, Táo chẳng hạn thì sẽ báo lỗi vì Táo kg có trong danh sách tham khảo)

Gửi kèm file để các pác cho hướng giải quyết giúp nhé!

concogia · 28/8/10

nationalfox đã viết:
Xin chào diễn đàn;

Rất mong được chỉ giáo cho tôi ở trường hợp sau:

- Sheet1: Có chứa danh sách, số liệu gốc (reference data) ở cột B chẳng hạn có danh sách (Dừa, xoài, ổi, mận, hồng xiêm, nho, chôm chôm...)
- Sheet2: Nhập dữ liệu liên quan đến danh sách ở sheet1 trên nhưng trong 01 ô chứa cùng lúc các loại trên (Ví dụ: Xoài, Ổi hoặc Mận, hồng xiêm, nho)

Vậy đoạn Code nào để kiểm tra (sau khi nhập xong vào ô dữ liệu ở sheet2) số liệu không nằm trong sheet1
(vd: Ổi, Táo chẳng hạn thì sẽ báo lỗi vì Táo kg có trong danh sách tham khảo)

Gửi kèm file để các pác cho hướng giải quyết giúp nhé!

Right Click sheet2 ==> View code chép cái này vào

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Tam, Kq, I, Vung
        If Not Intersect(Target, Range("d2:d200")) Is Nothing Then
            Set Vung = Sheets("sheet1").Range("b2:b13")
                Tam = Split(Target.Value, ",")
                    For I = 0 To UBound(Tam)
                        If Application.WorksheetFunction.CountIf(Vung, Trim(Tam(I))) = 0 Then Kq = Kq & Tam(I)
                    Next
        End If
        If Kq <> "" Then MsgBox ("Không có: " & Kq): ActiveCell.Offset(-1, 0).Select
End Sub

Nhập dữ liệu trong vùng D2

200 ==> Enter ==> xem kết quả

SA_DQ · 28/8/10

Góp vui 1 fát, bằng macro sự kiện luôn

PHP:

 Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns("D:D")) Is Nothing Then
   Dim StrC As String, Trai As String
   Dim VTr As Byte:                    Const Ph As String = ","
   Dim Rng As Range, sRng As Range, Sh
   
   Set Sh = Sheet1:                    Set Rng = Sh.Range(Sh.[B1], Sh.[B65500].End(xlUp))
   StrC = Target.Value & Ph
   Do
      VTr = InStr(StrC, Ph)
      If VTr < 1 Then Exit Do
      Trai = Trim(Left(StrC, VTr - 1))
      Set sRng = Rng.Find(Trai, , xlFormulas, xlWhole, , , False)
      If sRng Is Nothing Then
         MsgBox Trai & " is Nothing!"
         Exit Do
      End If
      StrC = Mid(StrC, VTr + 1, Len(StrC))
   Loop
 End If
End Sub

sealand · 28/8/10

Mình không xem được file vì Exc2007 nhưng qua bài của Concogia thì mình tán đồng với Concogia và xin phép lược gọn bớt code như sau:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d2:d200")) Is Nothing Then
If WorksheetFunction.CountIf(Sheet1.[B2:B13], Target) _
= 0 Then MsgBox "Khong co SP nay"
End If
End Sub

concogia · 28/8/10

sealand đã viết:
Mình không xem được file vì Exc2007 nhưng qua bài của Concogia thì mình tán đồng với Concogia và xin phép lược gọn bớt code như sau:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("d2:d200")) Is Nothing Then If WorksheetFunction.CountIf(Sheet1.[B2:B13], Target) _ = 0 Then MsgBox "Khong co SP nay" End If End Sub

Bài đây, Thầy ơi
Híc, sao Thầy không cài thêm "thằng" 2007

nationalfoxx · 28/8/10

Thanks Concogia.

Còn bị hạn chế nữa của đoạn code này là khi chọn 1 lúc nhiều (2 trở lên) ô đã nhập dữ liệu và xóa đi thì bị báo lỗi. Xin chỉ thêm.

concogia đã viết:

Right Click sheet2 ==> View code chép cái này vào

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Tam, Kq, I, Vung
        If Not Intersect(Target, Range("d2:d200")) Is Nothing Then
            Set Vung = Sheets("sheet1").Range("b2:b13")
                Tam = Split(Target.Value, ",")
                    For I = 0 To UBound(Tam)
                        If Application.WorksheetFunction.CountIf(Vung, Trim(Tam(I))) = 0 Then Kq = Kq & Tam(I)
                    Next
        End If
        If Kq <> "" Then MsgBox ("Không có: " & Kq): ActiveCell.Offset(-1, 0).Select
End Sub

Nhập dữ liệu trong vùng D2

200 ==> Enter ==> xem kết quả

concogia · 28/8/10

nationalfoxx đã viết:
Thanks Concogia.

Còn bị hạn chế nữa của đoạn code này là khi chọn 1 lúc nhiều (2 trở lên) ô đã nhập dữ liệu và xóa đi thì bị báo lỗi. Xin chỉ thêm.

Dim Tam, Kq, I, Vung
On Error Resume Next
If Not Intersect(Target, Range("d2:d200")) Is Nothing Then
Thêm dòng màu đỏ vào code giúp mình nhé, nó sẽ ổn thôi
Thân

sealand · 28/8/10

Trời, mình đoán là có 1 mã nào ngờ có 1 chuỗi. Vậy thì phải thêm 1 đoạn For để quét rồi.
Cám ơn Concogia nha

hoangminhtien · 28/8/10

Bài này có thể dùng Validation...Nếu dữ liệu của bạn nhập đúng

nationalfoxx · 28/8/10

Trên diễn đàn có cái vụ Exc 2003 mở được file xlsx mà pác http://www.giaiphapexcel.com/forum/archive/index.php/t-30511.html
hoặc vào http://www.microsoft.com/downloads/...70-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en

sealand đã viết:
Mình không xem được file vì Exc2007 nhưng qua bài của Concogia thì mình tán đồng với Concogia và xin phép lược gọn bớt code như sau:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("d2:d200")) Is Nothing Then If WorksheetFunction.CountIf(Sheet1.[B2:B13], Target) _ = 0 Then MsgBox "Khong co SP nay" End If End Sub

nationalfoxx · 29/8/10

Cám ơn 02 pác;

- Bài pác Concogia chạy smooth rùi nhưng nó kg chịu lưu theo file, nên mỗi lần chạy phải copy đoạn mã bỏ vào. $-\\/.$

- Bài pác hoangminhtien thì tốt đó nhưng nhập liệu phải đúng từng chữ in hay thường. Mong pác hướng dẫn thêm cách dùng Validation trong trường hợp này, xin đa tạ ><></

hoangminhtien đã viết:
Bài này có thể dùng Validation...Nếu dữ liệu của bạn nhập đúng

viehoai · 29/8/10

nationalfoxx đã viết:
Cám ơn 02 pác;

- Bài pác Concogia chạy smooth rùi nhưng nó kg chịu lưu theo file, nên mỗi lần chạy phải copy đoạn mã bỏ vào. $-\\/.$

- Bài pác hoangminhtien thì tốt đó nhưng nhập liệu phải đúng từng chữ in hay thường. Mong pác hướng dẫn thêm cách dùng Validation trong trường hợp này, xin đa tạ

1. Bạn quét chọn vùng trái cây sheet1 đặt cho tên vd như TraiCay
2. Sheet2 bạn chọn vùng cần dùng Validation. Từ Menu Data xổ xuống bạn chọn Validation.. bạn nhập như trong file đính kèm

concogia · 29/8/10

nationalfoxx đã viết:
Cám ơn 02 pác;

- Bài pác Concogia chạy smooth rùi nhưng nó kg chịu lưu theo file, nên mỗi lần chạy phải copy đoạn mã bỏ vào. $-\\/.$

Trong file EC2007 có macro thì phải lưu file có đuôi : xlsm

nationalfoxx · 29/8/10

Nó cho chỉ cho nhập 01 loại thui, nếu 02 loại trở lên sẽ báo lỗi. Pác chỉ cách khắc phục

Riêng file do pác minhtien.hoang thì OK, nhập rất tốt. Nhưng chưa bít cách dùng Validition của Pác này, mong pác chỉ giáo.

Tôi thấy Pác đặt Name dk= (LEN(Sheet2!$D2)*COUNTA(Sheet1!$B$2:$B$13)-SUM(LEN(SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2:$B$13,""))))-LEN(SUBSTITUTE(Sheet2!$D2,", ",""))

Pác giải thik thêm nhỉ!?

viehoai đã viết:
1. Bạn quét chọn vùng trái cây sheet1 đặt cho tên vd như TraiCay
2. Sheet2 bạn chọn vùng cần dùng Validation. Từ Menu Data xổ xuống bạn chọn Validation.. bạn nhập như trong file đính kèm

sealand · 29/8/10

Bạn thử dùng danh sách để nhập chính xác, nhanh

nguyen_dang · 30/8/10

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Tam, Kq, I, Vung
        If Not Intersect(Target, Range("d2:d200")) Is Nothing Then
            Set Vung = Sheets("sheet1").Range("b2:b13")
                Tam = Split(Target.Value, ",")
                    For I = 0 To UBound(Tam)
                        If Application.WorksheetFunction.CountIf(Vung, Trim(Tam(I))) = 0 Then Kq = Kq

nationalfoxx · 4/9/10

Chào Sealand;

Tôi thấy bạn dùng listbox rất hay, ở đây tôi muốn bạn đưa ra giải pháp cải tiến hơn trong trường hợp Danh sách quá dài, ta có thể cho cắt khoảng 15 rows phần còn lại sẽ nhảy qua cột khác được không? (Giống như View icon trong explore)

sealand đã viết:
Bạn thử dùng danh sách để nhập chính xác, nhanh

sealand · 4/9/10

nationalfoxx đã viết:
Chào Sealand;

Tôi thấy bạn dùng listbox rất hay, ở đây tôi muốn bạn đưa ra giải pháp cải tiến hơn trong trường hợp Danh sách quá dài, ta có thể cho cắt khoảng 15 rows phần còn lại sẽ nhảy qua cột khác được không? (Giống như View icon trong explore)

Mình cũng không hiểu tại sao ListBox của VB thì cho phép điều đó nhưng VBA lại không được.

hoangminhtien · 4/9/10

nationalfoxx đã viết:
Nó cho chỉ cho nhập 01 loại thui, nếu 02 loại trở lên sẽ báo lỗi. Pác chỉ cách khắc phục

Riêng file do pác minhtien.hoang thì OK, nhập rất tốt. Nhưng chưa bít cách dùng Validition của Pác này, mong pác chỉ giáo.

Tôi thấy Pác đặt Name dk= (LEN(Sheet2!$D2)*COUNTA(Sheet1!$B$2:$B$13)-SUM(LEN(SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2:$B$13,""))))-LEN(SUBSTITUTE(Sheet2!$D2,", ",""))

Pác giải thik thêm nhỉ!?

Trước hết bạn hiểu

SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2:$B$13,"")

là 1 hàm mảng, sẽ cho ra 1 mảng gồm các phần tử {SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2,""),SUBSTITUTE(Sheet2!$D2,Sheet1!$B$3,""),...SUBSTITUTE(Sheet2!$D2,Sheet1!$B$13,"")}.
Do đó

SUM(LEN(SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2:$B$13,""))))

sẽ cho tổng số ký tự các phần tử của mảng chuổi trên. Ta thấy tổng này sẽ bằng LEN(Sheet2!$D2)*COUNTA(Sheet1!$B$2:$B$13) trừ (-) đi số tổng số độ dài các chuỗi “Trái cây” có trong D2. Mà tổng số độ dài các chuỗi “Trái cây” có trong D2 lại bằng (nếu dữ liệu trong ô thỏa mãn điều kiện nhập đúng)

LEN(SUBSTITUTE(Sheet2!$D2,", ",""))

Vì vậy để dùng Validiation thì điều kiện phải là

=dk=0

Hi vọng bạn có thể hiểu ý của mình đã giải thích.

nationalfoxx · 4/9/10

Thanks Pác nhiều;

Tôi cũng nghĩ thế, nhưng do chạy thử công thức

= SUM(LEN(SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2:$B$13,"" ))))

Thấy kết quả không như suy nghĩ nên hỏi lại Pác. Mong thông cảm!

hoangminhtien đã viết:
Trước hết bạn hiểu là 1 hàm mảng, sẽ cho ra 1 mảng gồm các phần tử {SUBSTITUTE(Sheet2!$D2,Sheet1!$B$2,""),SUBSTITUTE(Sheet2!$D2,Sheet1!$B$3,""),...SUBSTITUTE(Sheet2!$D2,Sheet1!$B$13,"")}.
Do đó sẽ cho tổng số ký tự các phần tử của mảng chuổi trên. Ta thấy tổng này sẽ bằng LEN(Sheet2!$D2)*COUNTA(Sheet1!$B$2:$B$13) trừ (-) đi số tổng số độ dài các chuỗi “Trái cây” có trong D2. Mà tổng số độ dài các chuỗi “Trái cây” có trong D2 lại bằng (nếu dữ liệu trong ô thỏa mãn điều kiện nhập đúng) Vì vậy để dùng Validiation thì điều kiện phải là
Hi vọng bạn có thể hiểu ý của mình đã giải thích.

Code Kiểm tra số liệu nhập vào

Thành viên mới

File đính kèm

Gội rồi mới Cạo

/(hông là gì!

Thành viên gạo cội

Gội rồi mới Cạo

File đính kèm

Thành viên mới

Gội rồi mới Cạo

Thành viên gạo cội

Thành viên gắn bó

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gắn bó

File đính kèm

Gội rồi mới Cạo

Thành viên mới

Thành viên gạo cội

File đính kèm

Thành viên chính thức

Thành viên mới

Thành viên gạo cội

Thành viên gắn bó

Thành viên mới

Đếm ngược thời gian