Tìm lỗi CODE VBA tìm kiếm và tính toán

[hoahuongduong1986]

Kính gửi AC và các bạn trên diễn đàn,
File Excel em có code tìm và tính toán điểm như sau:
Tại Sheet Giao dịch em cần tính toán điểm các giao dịch tại cột X. Điều kiện tìm kiếm là các tiêu thức tại cột C (Giao dịch Iterm) và các tiêu thức tại I đến L. Vùng tham chiếu điểm và điều kiện tìm bên Sheet Ma2 tại Cột B đến E.
Công thức tính toán như sau: Điểm =( Số lượng đối tác/hoặc số lượng HS bàn giao/hoặc Số lượng KUNN/hoặc Số lượng tài sản - 1) * Hệ số điểm trường bổ sung (Tại sheet Ma2 tìm tương tứng với điều kiện tại Cột B và D). Em có code nhưng báo lỗi MisType. Anh chị xem giúp lỗi do đâu ạ. Em cảm ơn.

 

[SA_DQ]

Mình thử chạy macro bằng cách bấm vô nút lệnh & macro không hề báo lỗi
Cho kết qua [X5] = 10 (Xin hỏi vì sao = 10 vậy (?))

 

[hoahuongduong1986]

Mình thử chạy macro bằng cách bấm vô nút lệnh & macro không hề báo lỗi
Cho kết qua [X5] = 10 (Xin hỏi vì sao = 10 vậy (?))

Thưa anh, nếu Code này em làm khoảng 3000 dòng thì chạy được ạ. Mà em chạy khoảng 40.000 dòng thì nó cứ báo lỗi Mistype 13 cái đoạn này ạ "e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)". Mà COde em không có giới hạn dòng mà xử lý theo vùng có dữ liệu. Code này có vấn đề gì anh nhỉ.

 

[batman1]

Thưa anh, nếu Code này em làm khoảng 3000 dòng thì chạy được ạ. Mà em chạy khoảng 40.000 dòng thì nó cứ báo lỗi Mistype 13 cái đoạn này ạ "e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)".

Code chắc chắn không chuẩn. Bạn nói lỗi chỉ xuất hiện khi có cỡ 40.000 dòng dữ liệu còn khoảng 3000 dòng thì chạy êm. Nhưng thực ra lỗi không phải bao giờ cũng sảy ra, còn tùy vào dữ liệu cụ thể. Nó có thể sảy ra chỉ với 1 dòng dữ liệu.

Trong sheet Ma2 có B53:E53 = B.7.5 GN phong tỏa, 1, Số lượng đối tác, bổ sung item
Xét

With Sheet1
lr = .Range("B" & Rows.Count).End(xlUp).Row
a = .Range("B2:E" & lr).Value
End With
For i = 1 To UBound(a)
    If a(i, 3) <> "" And a(i, 4) <> "" Then
        Dic.Item(a(i, 1) & "|" & a(i, 3)) = a(i, 4)
    End If
Next i

Khi i = 52 thì có key = "B.7.5 GN phong tỏa|Số lượng đối tác" được thêm vào từ điển Dic kèm vói item = "bổ sung item" ***

Bây giờ ta thêm 1 dòng dữ liệu vào sheet Giao Dich: C88 = "B.7.5 GN phong tỏa" và I88 = 4.

Xet code

With Sheet3
   lr = .Range("C" & Rows.Count).End(xlUp).Row
   b = .Range("C2:C" & lr).Value
   c = .Range("I2:L" & lr).Value
   lr = UBound(b, 1)
   ReDim e(1 To lr, 1 To 1)
   d = .Range("I1:L1").Value
   kd = UBound(d, 2)
End With

Dòng mới thêm ở trên nằm ở dòng 87 của mảng b và c. Cụ thể: b(87, 1) = "B.7.5 GN phong tỏa", c(87, 1) = 4. ****
d(1, 1) = 'Giao dich'!I1 = "Số lượng đối tác"

Xét code

For i = 1 To lr
    For j = 1 To kd
        temp = b(i, 1) & "|" & d(1, j)
        If Dic.Exists(temp) Then
            If c(i, j) <> 0 Then
                e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
                Exit For
            End If
        End If
    Next j
Next i

Khi i = 87 và j = 1 thì:
temp = b(i, 1) & "|" & d(1, j) = b(87, 1) & "|" & d(1, 1) = "B.7.5 GN phong tỏa" & "|" & "Số lượng đối tác" = "B.7.5 GN phong tỏa|Số lượng đối tác"
Rõ ràng trong từ điển dic tồn tại key như thế (xem *** ở trên). Vậy code sau được thực hiện

If c(i, j) <> 0 Then
                e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
                Exit For
End If

c(i, j) = c(87, 1) = 4 (xem **** ở trên) <> 0. Vậy code sau được thực hiện

e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
Exit For

e(87, 1) = (c(87, 1) - 1) * Dic.Item( "B.7.5 GN phong tỏa|Số lượng đối tác") =
(4 - 1) * "bổ sung item" (xem *** ở trên) = 3 * "bổ sung item"

Rõ ràng có lỗi "Type mismatch" vì "bổ sung item" không là số. Không thể nhân với text được.

Tóm lại không cần tới 40.000 dòng dữ liệu. Chỉ cần thêm 1 dòng dữ liệu vào sheet Giao Dich: C88 = "B.7.5 GN phong tỏa" và I88 = 4. Sau đó chạy code thì sẽ có lỗi. Nguyên nhân ở đây là cột Ma!E có chứa text.

 

[hoahuongduong1986]

Code chắc chắn không chuẩn. Bạn nói lỗi chỉ xuất hiện khi có cỡ 40.000 dòng dữ liệu còn khoảng 3000 dòng thì chạy êm. Nhưng thực ra lỗi không phải bao giờ cũng sảy ra, còn tùy vào dữ liệu cụ thể. Nó có thể sảy ra chỉ với 1 dòng dữ liệu.

Trong sheet Ma2 có B53:E53 = B.7.5 GN phong tỏa, 1, Số lượng đối tác, bổ sung item
Xét

With Sheet1
lr = .Range("B" & Rows.Count).End(xlUp).Row
a = .Range("B2:E" & lr).Value
End With
For i = 1 To UBound(a)
    If a(i, 3) <> "" And a(i, 4) <> "" Then
        Dic.Item(a(i, 1) & "|" & a(i, 3)) = a(i, 4)
    End If
Next i

Khi i = 52 thì có key = "B.7.5 GN phong tỏa|Số lượng đối tác" được thêm vào từ điển Dic kèm vói item = "bổ sung item" ***

Bây giờ ta thêm 1 dòng dữ liệu vào sheet Giao Dich: C88 = "B.7.5 GN phong tỏa" và I88 = 4.

Xet code

With Sheet3
   lr = .Range("C" & Rows.Count).End(xlUp).Row
   b = .Range("C2:C" & lr).Value
   c = .Range("I2:L" & lr).Value
   lr = UBound(b, 1)
   ReDim e(1 To lr, 1 To 1)
   d = .Range("I1:L1").Value
   kd = UBound(d, 2)
End With

Dòng mới thêm ở trên nằm ở dòng 87 của mảng b và c. Cụ thể: b(87, 1) = "B.7.5 GN phong tỏa", c(87, 1) = 4. ****
d(1, 1) = 'Giao dich'!I1 = "Số lượng đối tác"

Xét code

For i = 1 To lr
    For j = 1 To kd
        temp = b(i, 1) & "|" & d(1, j)
        If Dic.Exists(temp) Then
            If c(i, j) <> 0 Then
                e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
                Exit For
            End If
        End If
    Next j
Next i

Khi i = 87 và j = 1 thì:
temp = b(i, 1) & "|" & d(1, j) = b(87, 1) & "|" & d(1, 1) = "B.7.5 GN phong tỏa" & "|" & "Số lượng đối tác" = "B.7.5 GN phong tỏa|Số lượng đối tác"
Rõ ràng trong từ điển dic tồn tại key như thế (xem *** ở trên). Vậy code sau được thực hiện

If c(i, j) <> 0 Then
                e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
                Exit For
End If

c(i, j) = c(87, 1) = 4 (xem **** ở trên) <> 0. Vậy code sau được thực hiện

e(i, 1) = (c(i, j) - 1) * Dic.Item(temp)
Exit For

e(87, 1) = (c(87, 1) - 1) * Dic.Item( "B.7.5 GN phong tỏa|Số lượng đối tác") =
(4 - 1) * "bổ sung item" (xem *** ở trên) = 3 * "bổ sung item"

Rõ ràng có lỗi "Type mismatch" vì "bổ sung item" không là số. Không thể nhân với text được.

Tóm lại không cần tới 40.000 dòng dữ liệu. Chỉ cần thêm 1 dòng dữ liệu vào sheet Giao Dich: C88 = "B.7.5 GN phong tỏa" và I88 = 4. Sau đó chạy code thì sẽ có lỗi. Nguyên nhân ở đây là cột Ma!E có chứa text.

Em cảm ơn sự chu đáo của anh ạ. Đúng như anh nói ạ.