Có cách nào để kiểm tra sheet/workbook có bị #SPILL! hay không? (1 người xem)

[vic_it]

Kính gửi Anh/Chị,
Trong quá trình xử lý file excel thì e gặp phải 1 vấn đề tràn dữ liệu sang bảng khác nên báo trạng thái lỗi tràn #SPILL!
Vì dữ liệu e chứa nhiều bảng khác nhau, nên việc dò lại lỗi khá tốn thời gian.
Vậy liệu mình có hàm sẵn/ hoặc vba nào có thể thực hiện việc dò tìm trạng thái #SPILL! này cho cả Sheet/Workbook không ạ?
Em cám ơn,

 

[thuytrangbp28]

Lỗi #SPILL! là do vùng cần điền kết quả của công thức mảng động bị giới hạn (có dữ liệu trong vùng cần điền kết quả), khi bạn nhập công thức xong thì nó báo luôn, kể cả bạn vô tình nhập dữ liệu vào vùng đó thì nó cũng báo luôn, sao đến mức để lan man khắp workbook giờ phải đi dò lại thế.
Còn để dò lỗi đó, bạn tham khảo code VBA này:

Option Explicit

Sub DoLoiSPILL()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim ketQua As String
    
    ketQua = ""

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
        
        If Not rng Is Nothing Then
            For Each cell In rng
                If cell.text = "#SPILL!" Then
                    ketQua = ketQua & ws.Name & " - " & cell.Address(False, False) & vbCrLf
                End If
            Next cell
            Set rng = Nothing
        End If
    Next ws

    If ketQua = "" Then
        MsgBox "Khong tim thay loi #SPILL! trong Workbook.", vbInformation
    Else
        MsgBox ketQua, vbExclamation, "Loi #SPILL!"
    End If
End Sub

 

[vic_it]

Lỗi #SPILL! là do vùng cần điền kết quả của công thức mảng động bị giới hạn (có dữ liệu trong vùng cần điền kết quả), khi bạn nhập công thức xong thì nó báo luôn, kể cả bạn vô tình nhập dữ liệu vào vùng đó thì nó cũng báo luôn, sao đến mức để lan man khắp workbook giờ phải đi dò lại thế.
Còn để dò lỗi đó, bạn tham khảo code VBA này:

Option Explicit

Sub DoLoiSPILL()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim ketQua As String
   
    ketQua = ""

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
       
        If Not rng Is Nothing Then
            For Each cell In rng
                If cell.text = "#SPILL!" Then
                    ketQua = ketQua & ws.Name & " - " & cell.Address(False, False) & vbCrLf
                End If
            Next cell
            Set rng = Nothing
        End If
    Next ws

    If ketQua = "" Then
        MsgBox "Khong tim thay loi #SPILL! trong Workbook.", vbInformation
    Else
        MsgBox ketQua, vbExclamation, "Loi #SPILL!"
    End If
End Sub

Dạ, do thiết kế file ban đầu ko theo cấu trúc, sau này các bảng nó lan rộng ra (khoảng 30 bảng các kiểu dữ liệu to nhỏ, dùng Trace Precedent khá mất thời gian).
Thực hiện với hơn 100 mẫu thì thi thoảng mới gặp Spill, có ảnh hưởng đến đầu ra nhưng không tác động nhiều, do dữ liệu mang tính tham chiếu tương đối.
File đính kèm là không lỗi/có lỗi và tìm ra vị trí spill trên sheet lỗi.
Đây đúng đoạn mã code em cần, cám ơn Anh/Chị nhiều.