Dùng tối đa bao nhiêu Or trong 1 câu lệnh VBA?

[ptm0412]

Tôi có 1 file Excel cần giải quyết như sau:
Trên diễn đàn này trong topic Searching for 50 combinations 1 thành viên có vấn đề như sau:

1. Vấn đề:Từ 1 danh sách Post codes, Order No, ... khoảng 10.000 dòng, anh ta muốn dùng AutoFilter để lọc ra 1 danh sách khoảng 50 loại postcode (trong số 200 gì đó) dựa trên 3 hoặc 4 ký tự đầu và chỉ lấy OrderNo trong khoảng 10 loại dựa trên 1 ký tự đầu.

2. Anh ta đang dùng 1 cột phụ với công thức
=If(And(Or(Left(PostCodes)= xxx), or(left(OrderNo) = yyy))
tạo các giá trị 1, 0 để dùng AutoFilter lọc các giá trị Post codes, Order No tương ứng (khoảng 50 sự kết hợp And và Or), nhưng như vậy là thủ công và công thức quá dài chiếm quá 1/2 trang màn hình.

3. Yêu cầu: Dùng VBA tạo 1 cột phụ như trên.

Tôi đã làm như trong file:
- dùng left tạo 2 cột phụ (họ không care chuyện bao nhiêu cột phụ, miễn đạt kết quả)
- Dùng VBA tạo cột phụ thứ 3 theo yêu cầu, Autofilter ra kết quả mong muốn.

Tuy nhiên với trình độ VBA hạng bét tôi chỉ có thể dùng lệnh gán công thức Excel cũng if(And(Or())) vào cell rồi copy paste value lại.
Hơn nữa công thức Excel có giới hạn 1 cái or chỉ cho 30 tham số là giá trị logic trong khi yêu cầu lên đến 50.

Vậy tôi xin hỏi trong VBA có giới hạn này không, và có thể sửa code theo hướng dùng Or của VBA như thế nào?
Vì nếu dùng Or của VBA ra kết quả 0 hoặc 1 rồi mới gán kết quả này vào cell, code sẽ chạy nhanh hơn.

Xin cám ơn!

 

[ThuNghi]

Sao Bác không dùng countif hay là match để thay thế
=IF(AND(OR($B$6="PE1",$B$6="RG19",$B$6="OX14",$B$6="AL3",$B$6="NR29"),OR($C$6="W",$C$6="F",$C$6="W")),1,0)
Khủng quá không nhìn ra nổi.
if(and(countif(DK01,B6)>0,countif(DK02,C6)>0),1,0)
Thấy nhẹ nhàng hơn.

 Sub Add_TN()
Dim EndR As Single, Temp As String, k As Single
Dim i As Single, j As Single, EndR1 As Single, EndR2 As Single
Dim DK01 As Range, DK02 As Range
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Sheet1.Select
Range("H2:H65000").ClearContents
EndR = Cells(1, 1).End(xlDown).Row
EndR1 = Cells(1, 9).End(xlDown).Row
EndR2 = Cells(1, 10).End(xlDown).Row
Set DK01 = Range(Cells(2, 9), Cells(EndR1, 9))
Set DK02 = Range(Cells(2, 10), Cells(EndR2, 10))

For k = 2 To EndR
    If WF.CountIf(DK01, Cells(k, 2)) > 0 And WF.CountIf(DK02, Cells(k, 3)) > 0 Then
        Cells(k, 8) = 1
    End If
Next k
Set DK01 = Nothing
Set DK02 = Nothing
Set WF = Nothing



End Sub

 

[phamduylong]

Tôi có 1 file Excel cần giải quyết như sau:
...
1. Vấn đề:Từ 1 danh sách Post codes, Order No, ... khoảng 10.000 dòng, anh ta muốn dùng AutoFilter để lọc ra 1 danh sách khoảng 50 loại postcode (trong số 200 gì đó) dựa trên 3 hoặc 4 ký tự đầu và chỉ lấy OrderNo trong khoảng 10 loại dựa trên 1 ký tự đầu.

2. Anh ta đang dùng 1 cột phụ với công thức
=If(And(Or(Left(PostCodes)= xxx), or(left(OrderNo) = yyy))
tạo các giá trị 1, 0 để dùng AutoFilter lọc các giá trị Post codes, Order No tương ứng (khoảng 50 sự kết hợp And và Or), nhưng như vậy là thủ công và công thức quá dài chiếm quá 1/2 trang màn hình.

3. Yêu cầu: Dùng VBA tạo 1 cột phụ như trên.

Tôi đã làm như trong file:
- dùng left tạo 2 cột phụ (họ không care chuyện bao nhiêu cột phụ, miễn đạt kết quả)
- Dùng VBA tạo cột phụ thứ 3 theo yêu cầu, Autofilter ra kết quả mong muốn.
...
Xin cám ơn!

Dùng VBA thì bạn chỉ cần tạo 1 cột phụ là đủ vì VBA cho phép viết lệnh so sánh dễ hơn hàm Excel nhiều.
Trong bài này tôi dùng lệnh Find của Excel để nhảy tới dòng trong cột A trùng các tiêu chuẩn Criterial1 nên vòng lặp chạy rất ít (dùng vòng lặp duyệt hết các ô trong cột A thì có bấy nhiêu dòng sẽ lặp bấy nhiêu lần)

Sub LocDieuKien()
Dim rI As Long, rF As Long, rC As Long, rD As Long
Dim MyCell As Range
On Error Resume Next
Columns("F:F").ClearContents
rC = Cells(1, 1).End(xlDown).Row  'Dòng cuoi cung cua du lieu
Set Crit2 = Range(Cells(2, 8), Cells(Cells(1, 8).End(xlDown).Row, 8))
Range(Cells(2, 7), Cells(Cells(1, 7).End(xlDown).Row, 7)).Select
For Each MyCell In Selection
  rI = 1: rD = 1
  Do
    rF = Columns("A:A").Find(What:=MyCell.Value, After:=Cells(rI, 1), SearchOrder:=xlByColumns).Row
    If rF = 0 Or rF = rD Then
      Err.Number = 0
      Exit Do
    Else
      If rD = 1 Then rD = rF
      If InStr(1, Cells(rF, 1), [COLOR="Red"][B]MyCell.Value[/B][/COLOR] & " ") = 1 Then
        If Application.WorksheetFunction.CountIf(Crit2, Left(Cells(rF, 2), 1)) Then Cells(rF, 6) = 1
      End If
      rI = rF
    End If
  Loop
Next
Set Crit2 = Nothing
End Sub

 

[Mr Okebab]

Tôi có 1 file Excel cần giải quyết như sau:
Trên diễn đàn này trong topic Searching for 50 combinations 1 thành viên có vấn đề như sau:

1. Vấn đề:Từ 1 danh sách Post codes, Order No, ... khoảng 10.000 dòng, anh ta muốn dùng AutoFilter để lọc ra 1 danh sách khoảng 50 loại postcode (trong số 200 gì đó) dựa trên 3 hoặc 4 ký tự đầu và chỉ lấy OrderNo trong khoảng 10 loại dựa trên 1 ký tự đầu.

2. Anh ta đang dùng 1 cột phụ với công thức
=If(And(Or(Left(PostCodes)= xxx), or(left(OrderNo) = yyy))
tạo các giá trị 1, 0 để dùng AutoFilter lọc các giá trị Post codes, Order No tương ứng (khoảng 50 sự kết hợp And và Or), nhưng như vậy là thủ công và công thức quá dài chiếm quá 1/2 trang màn hình.

3. Yêu cầu: Dùng VBA tạo 1 cột phụ như trên.

Tôi đã làm như trong file:
- dùng left tạo 2 cột phụ (họ không care chuyện bao nhiêu cột phụ, miễn đạt kết quả)
- Dùng VBA tạo cột phụ thứ 3 theo yêu cầu, Autofilter ra kết quả mong muốn.

Tuy nhiên với trình độ VBA hạng bét tôi chỉ có thể dùng lệnh gán công thức Excel cũng if(And(Or())) vào cell rồi copy paste value lại.
Hơn nữa công thức Excel có giới hạn 1 cái or chỉ cho 30 tham số là giá trị logic trong khi yêu cầu lên đến 50.

Vậy tôi xin hỏi trong VBA có giới hạn này không, và có thể sửa code theo hướng dùng Or của VBA như thế nào?
Vì nếu dùng Or của VBA ra kết quả 0 hoặc 1 rồi mới gán kết quả này vào cell, code sẽ chạy nhanh hơn.

Xin cám ơn!

Cũng đơn giản thôi bác ạ:

Cách 1 : Dùng VBA (UDF)

Function DKOk(ID1 As String, MangDK1 As Range, ID2 As String, MangDK2 As Range) As Boolean
    On Error GoTo EndFunc
        Application.Volatile (False)
        If ID1 = "" Or ID2 = "" Then Exit Function
        If MangDK1.Columns.Count > 1 Or MangDK2.Columns.Count > 1 Then Exit Function
        If WorksheetFunction.Match(ID1, MangDK1, 0) Then
        If WorksheetFunction.Match(ID2, MangDK2, 0) Then _
            DKOk = True
        End If
EndFunc:
End Function

Cách 2 : Dùng Công thức

=IF(ISERROR(MATCH(B2;DieuKien1;0)+MATCH(C2;DieuKien2;0));"";1)

Cách 3 : Dùng Name : Rút gọn từ công thức

Cách 4 : Dùng VBA (Sub) : Các bác đã làm rồi


Thân!

P/S : Bác có thể không cần 2 cột phụ kia cũng được

 

[ptm0412]

Xin cám ơn tất cả mọi người:

To Thu Nghi và Mr Bab: Countif và Match quá gọn mà sao mình không thấy nhỉ? Lẩm cẩm quá chăng? Với 2 hàm này thì chả cần VBA.

=IF(ISERROR(MATCH(B2;DieuKien1;0)+MATCH(C2;DieuKien2;0));"";1)
=if(and(countif(DK01,B6)>0,countif(DK02,C6)>0),1,0)

To Thầy Long: Xin cám ơn thầy vì bài học cơ bản ở trên.

Trong khi đó tôi cũng tìm được 1 cách dùng Or của VBA theo hướng bài #1 (lúc chưa đọc các bài trả lời), nên vẫn không gọn và vẫn phải dùng For quét hết các ô, xin gởi lên không dám dấu dốt:

Sub Add1()
Dim EndR As Single, Temp As String, k As Single
Dim i As Single, j As Single, EndR1 As Single, EndR2 As Single
Dim CondN As Boolean, CondN1 As Boolean, CondN2 As Boolean
    EndR = Cells(1, 1).End(xlDown).Row
    EndR1 = Cells(1, 9).End(xlDown).Row
    EndR2 = Cells(1, 10).End(xlDown).Row
    For k = 2 To EndR
        CondN = False: CondN1 = False: CondN2 = False
        For i = 2 To EndR1
            CondN1 = CondN1 Or (Cells(k, 2) = Cells(i, 9))
        Next i
        For j = 2 To EndR2
            CondN2 = CondN2 Or (Cells(k, 3) = Cells(j, 10))
        Next j
        CondN = CondN1 And CondN2
        Cells(k, 8).Value = IIf(CondN, 1, 0)
    Next k
End Sub

 

[Mr Okebab]

Xin cám ơn tất cả mọi người:

To Thu Nghi và Mr Bab: Countif và Match quá gọn mà sao mình không thấy nhỉ? Lẩm cẩm quá chăng? Với 2 hàm này thì chả cần VBA.


To Thầy Long: Xin cám ơn thầy vì bài học cơ bản ở trên.

Trong khi đó tôi cũng tìm được 1 cách dùng Or của VBA theo hướng bài #1 (lúc chưa đọc các bài trả lời), nên vẫn không gọn và vẫn phải dùng For quét hết các ô, xin gởi lên không dám dấu dốt:

Sub Add1()
Dim EndR As Single, Temp As String, k As Single
Dim i As Single, j As Single, EndR1 As Single, EndR2 As Single
Dim CondN As Boolean, CondN1 As Boolean, CondN2 As Boolean
    EndR = Cells(1, 1).End(xlDown).Row
    EndR1 = Cells(1, 9).End(xlDown).Row
    EndR2 = Cells(1, 10).End(xlDown).Row
    For k = 2 To EndR
        CondN = False: CondN1 = False: CondN2 = False
        For i = 2 To EndR1
            CondN1 = CondN1 Or (Cells(k, 2) = Cells(i, 9))
        Next i
        For j = 2 To EndR2
            CondN2 = CondN2 Or (Cells(k, 3) = Cells(j, 10))
        Next j
        CondN = CondN1 And CondN2
        Cells(k, 8).Value = IIf(CondN, 1, 0)
    Next k
End Sub

Đúng như bác ThuNghi nói, match sẽ nhanh hơn countif nhiều vì nó không quét hết mảng

Trước đây em cũng hay dùng For . . Next và For each . . .Next, tuy nhiên rút ra kinh nghiệm là :

- Khi các điều kiện cần tìm là chính xác và chỉ cần 1 điều kiện (như VD trên) thì ta nên dùng match. Khi không kiểm soát được lỗi do Match gây ra (tìm không thấy) thì sẽ dùng CountIf

- Khi có nhiều điều kiện, và các điều kiện này là phức tạp (VD như ô đó có chứa những ký tự gì đó ở vị trí bất kỳ, ô bên cạnh có số trong khoảng từ A -->B, . . . . ) thì khi đó nên dùng For . . Next hoặc For each . . . next

Thân!P/S : VD trên tốt nhất là bỏ đi cột phụ bác ạ, như vậy sẽ gọn hơn
Nếu dùng VBA thì nên tạo 1 UDF cho việc tách này cho gọn.

 

[ptm0412]

- Khi các điều kiện cần tìm là chính xác và chỉ cần 1 điều kiện (như VD trên) thì ta nên dùng match. Khi không kiểm soát được lỗi do Match gây ra (tìm không thấy) thì sẽ dùng CountIf

Phải rồi, rốt cuộc là sau khi quy về, chỉ còn 1 điều kiện. Lúc đầu mình lại cứ cho là 50 điều kiện mới nên nông nỗi ấy.

TB: Đã trả lời bài bên kia, dùng file của Thầy Long (có nói rõ nguồn gốc), xin cám ơn thầy.

 

[phamduylong]

Trong bài 4, do không kiểm tra lại nên có 1 dòng lệnh sai:

If InStr(1, Cells(rF, 1), Cells(n, 7)  & " ") = 1 Then

chỉnh lại:

If InStr(1, Cells(rF, 1), [B][COLOR="Red"]MyCell.Value[/COLOR][/B] & " ") = 1 Then

và đã tải lại Postcode4 New.zip
Sub LocDieuKien lọc điều kiện trực tiếp từ 2 cột Post Code và Order No nên đã bỏ 2 cột phụ LeftPostcode và LeftOrderNo.