Kiểm tra dữ liệu nhập

[PhanTuHuong]

Sưu tầm từ Pcworld​

Hỏi:

Tôi có một bảng tính có cột mã số hàng hoá được nhập liệu hàng ngày. Tôi muốn kiểm tra việc nhập liệu có bị sai so với cột mã số hàng hoá chuẩn hay không thì phải làm sao?

Đáp:

Excel cho phép ta khai báo một số hàm sự kiện cho một số sự kiện trên từng worksheet Excel, thí dụ hàm Worksheet_SelectionChange() sẽ được kích hoạt mỗi lần ta thay đổi cell làm việc trong worksheet tương ứng, do đó bạn có thể kiểm tra việc nhập liệu trong 1 cột nào đó có đúng qui định không thông qua hàm xử lý sự kiện Worksheet_SelectionChange(). Thí dụ để kiểm tra việc nhập mã hàng hóa theo yêu cầu của bạn, bạn hãy tiến hành qui trình các bước cụ thể như sau:

- Chạy Excel, mở file Excel chứa bảng tính dữ liệu của bạn.
- nhập danh sách mã hàng hóa hợp lệ vào worksheet, thí dụ từ cell P1 tới cell P20.
- chọn menu Tools.Macro.Visual Basic Editor để mở cửa sổ soạn code VBA.
- trong cửa sổ Project, dời chuột về mục Sheet1 (sheet chứa bảng số liệu), ấn kép chuột vào nó để mở cửa sổ soạn code cho worksheet tương ứng.
- ấn chuột vào ListBox "Object" để hiển thị các đối tượng lập trình hiện có trong worksheet tương ứng, chọn mục Worksheet để tạo hàm Worksheet_SelectionChange() cho worksheet này.
- Soạn code cho hàm Worksheet_SelectionChange() theo yêu cầu kiểm tra của bạn:

'khai báo hằng miêu tả cột đầu và cuối của bảng dữ liệu
Const StartRow = 2
Const EndRow = 33
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Val As String
Dim r As Integer
'kiểm tra nếu cell không phải là cột A thì thôi
If Target.Offset.Column <> 1 Then Exit Sub
r = Target.Offset.Row - 1
'kiểm tra nếu cell không nằm trong phạm vi thì thôi
If (StartRow <= r) And (r <= EndRow) Then
Val = Target.Cells(0, 1).Value
'kiểm tra nội dung cell có hợp lệ không
kq = Application.VLookup(Val, Range("$P$1:$P$20"), 1)
If IsError(kq) Or (kq <> Val) Then MsgBox "Ban da nhap sai ma hang : " & Val
End If
End Sub

Do đối số của hàm Worksheet_SelectionChange() xác định vị trí cell hiện hành chứ không phải là vị trí cell cũ (vừa bị thay đổi nội dung), muốn để hàm trên hoạt động đúng theo yêu cầu thì sau khi nhập mới nội dung 1 cell của cột mã hàng hóa, bạn phải dùng phím Enter hay ¯ để kích hoạt được hàm kiểm tra trên.

 

[PhanTuHuong]

Hỏi:

Tôi có bảng tính trong Excel, tại 1 ô ở ngoài bảng tính đó tôi muốn viết ra một chữ thỏa 2 điều kiện:
- Chữ này nằm ở một cột nào đó (vd: cột A)
- Chữ này phải cùng hàng với một chữ (vd: chữ "Test") ở cột khác (vd: cột B).
Xin hướng dẫn.

Đáp:

Mỗi bảng tính (worksheet) Excel chứa 65536 hàng, mỗi hàng chứa 256 cột, nghĩa là mỗi bảng tính Excel có 65536*256 = 16 triệu cell dữ liệu. Bạn có thể xem và hiệu chỉnh nội dung của bất kỳ cell nào của bảng tính tại bất kỳ thời điểm nào. Việc hiệu chỉnh có thể là thủ công bằng tay hay lập trình bằng đoạn lệnh VBA.

Thường bạn chỉ dùng 1 phần nhỏ của worksheet Excel để chứa bảng dữ liệu và phần còn lại bỏ trống. Để hiệu chỉnh tự động 1 cell nào đó bằng VBA, bạn cần xác định vị trí của cell đó (chỉ số hàng, cột của cell) và nội dung mới cần hiệu chỉnh. Thí dụ macro sau sẽ hiệu chỉnh cell Ai của worksheet hiện hành (i là hàng mà cell Bi chứa nội dung là "Test"):

Sub UpdateCell()
 Dim rng As Range
 Dim addr As String
 'thiết lập vùng cell cần tra cứu
 Set rng = Range("$B$1:$B$1000")
 For i = 1 To rng.Count
 'kiểm tra nội dung từng cell với "Test"
 If ("Test" = rng.Item(i, 1).Value) Then
 'xác định vị trí cell cần hiệu chỉnh
 addr = "$A$" & i
 'hiệu chỉnh cell theo giá trị mới
 Range(addr).Value = "New value"
 Exit For
 End If
 Next i
End Sub

 

[SA_DQ]

Sưu tầm từ Pcworld​

Worksheet_SelectionChange()

'khai báo hằng miêu tả cột đầu và cuối của bảng dữ liệu
Const StartRow = 2
Const EndRow = 33
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Val As String
Dim r As Integer
'kiểm tra nếu cell không phải là cột A thì thôi
If Target.Offset.Column <> 1 Then Exit Sub
r = Target.Offset.Row - 1
'kiểm tra nếu cell không nằm trong phạm vi thì thôi
If (StartRow <= r) And (r <= EndRow) Then
Val = Target.Cells(0, 1).Value
'kiểm tra nội dung cell có hợp lệ không
kq = Application.VLookup(Val, Range("$P$1:$P$20"), 1)
If IsError(kq) Or (kq <> Val) Then MsgBox "Ban da nhap sai ma hang : " & Val
End If
End Sub

Bạn đã thử biên tập lại chưa? Hình như nó phải vầy nè:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim SoLieu As Variant
 Dim Rng As Range, rCells As Range
 If Intersect(Target, Range("B" & StartRow & ":B" & EndRow)) Is Nothing Then Exit Sub
 Set rCells = Range("P1:P20")
 For Each Rng In rCells
    If Rng.Value = Target.Value Then
        SoLieu = "OK":          Exit For
    End If
 Next Rng
 If SoLieu <> "OK" Then MsgBox "BAN DA NHAP SAI SO LIEU!" 
End Sub

From PhanTuHuong:
Cám ơn bác, đúng là em chưa kịp test lại.

 

[SA_DQ]

Tiện xin Test câu 2 luôn nha!

Sub UpdateCell()
Dim rng As Range: Dim addr As String
Set rng = Range("$B$1:$B$1000")
For i = 1 To rng.Count
If ("Test" = rng.Item(i, 1).Value) Then
addr = "$A$" & i
Range(addr).Value = "New value"
Exit For
End If
Next i
End Sub

[B]Sub UpdateCell()[/B]
 Dim Rng As Range: Dim addr As String

 For Each Rng In Range("B1:B999")
    With Rng
       If .Value = "Test" Then .Offset(, -1).Value = "New "
    End With
 Next Rng
 Exit Sub: [B]End Sub[/B]