Nhờ hướng dẫn tìm kiếm tham chiếu của công thức

[minhphung0111]

Nhờ Anh/Chị hỗ trợ giúp trường hợp của em:
Trong 1 Workbook, có nhiều sheet 1 -> 100. Em muốn tìm TẤT CẢ CÁC CELL tại sheet 1 (tìm cùng 1 lúc, do 1 sheet có rất nhiều data), đang nằm trong các cell có dạng Formular nào ở sheet khác
Do em muốn xoá bớt dữ liệu tại sheet 1 để nhẹ file mà ko ảnh hưởng đến các sheet khác
em sử dụng Trace Dependents & Trace Precedents trong tab Formulas thì chỉ xem được mỗi lần 1 cell thôi
Trân trọng cám ơn !

 

[VetMini]

Bạn có biết là cái này nó có tính chất đệ quy hôn?
Đại khái, A tham chiếu đến B. Nhưng không ai nào tham chiếu đến A. Xoá A xong thì lòi ra không ai tham chiếu đến B.

 

[huuthang_bd]

Code không áp dụng với name nha bạn.

Const TenGi = "DatTenGiChaDuoc"
Sub UsedCll()
Dim ThisSh As Worksheet, ShName As String, Sh As Worksheet, Cll As Range, CllAddress As String
Set ThisSh = ActiveSheet
ShName = ThisSh.Name
ThisSh.Name = TenGi
Application.ReferenceStyle = xlA1
For Each Sh In Worksheets
    If Not Sh Is ThisSh Then
        Set Cll = Sh.Cells.Find(What:=TenGi, LookIn:=xlFormulas, LookAt:=xlPart, SearchFormat:=False)
        If Not Cll Is Nothing Then
            CllAddress = Cll.Address
            Do
                FindRef ThisSh, Cll.Formula
                Set Cll = Sh.Cells.FindNext(Cll)
            Loop Until Cll.Address = CllAddress
        End If
    End If
Next
End Sub
Private Sub FindRef(ByRef ThisSh As Worksheet, ByVal sFormula As String)
Dim Arr As Variant, sRef As String, i As Long, j As Long
Arr = Split(sFormula, TenGi & "!")
For i = 1 To UBound(Arr, 1)
    sRef = Arr(i) & " "
    For j = 2 To Len(sRef)
        If Mid(sRef, j) Like "[!A-Z0-9:$]" Then
            ThisSh.Range(Left(sRef, j - 1)).Interior.Color = vbRed
        End If
    Next
Next
End Sub

 

[minhphung0111]

Code không áp dụng với name nha bạn.

Const TenGi = "DatTenGiChaDuoc"
Sub UsedCll()
Dim ThisSh As Worksheet, ShName As String, Sh As Worksheet, Cll As Range, CllAddress As String
Set ThisSh = ActiveSheet
ShName = ThisSh.Name
ThisSh.Name = TenGi
Application.ReferenceStyle = xlA1
For Each Sh In Worksheets
    If Not Sh Is ThisSh Then
        Set Cll = Sh.Cells.Find(What:=TenGi, LookIn:=xlFormulas, LookAt:=xlPart, SearchFormat:=False)
        If Not Cll Is Nothing Then
            CllAddress = Cll.Address
            Do
                FindRef ThisSh, Cll.Formula
                Set Cll = Sh.Cells.FindNext(Cll)
            Loop Until Cll.Address = CllAddress
        End If
    End If
Next
End Sub
Private Sub FindRef(ByRef ThisSh As Worksheet, ByVal sFormula As String)
Dim Arr As Variant, sRef As String, i As Long, j As Long
Arr = Split(sFormula, TenGi & "!")
For i = 1 To UBound(Arr, 1)
    sRef = Arr(i) & " "
    For j = 2 To Len(sRef)
        If Mid(sRef, j) Like "[!A-Z0-9:$]" Then
            ThisSh.Range(Left(sRef, j - 1)).Interior.Color = vbRed
        End If
    Next
Next
End Sub

Hi Anh huuthang_bd, nhờ anh hướng dẫn giùm em code này ý nghĩa ntn. Em trỏ chuột vào sheet cần test & và mở code lên chạy, sau khi chạy, thấy tên sheet đó thay đổi thành "DatTenGiChaDuoc", ngoài ra không thấy có gì khác, không biết em có làm sai gì không, nhờ anh hướng dẫn giúp. Thanks anh !

Bài đã được tự động gộp: 24/6/20

Bạn có biết là cái này nó có tính chất đệ quy hôn?
Đại khái, A tham chiếu đến B. Nhưng không ai nào tham chiếu đến A. Xoá A xong thì lòi ra không ai tham chiếu đến B.

Hi anh VetMini, em chưa hiểu ý của anh, hic. Mục đích của em là nếu sheet cần test không có cell nào liên quan đến các cell có chứa Formular nằm trong các sheet khác, thì có thể an tâm xoá sheet đó đi cho nhẹ file. Cám ơn anh !

 

[be_09]

Nhờ Anh/Chị hỗ trợ giúp trường hợp của em:
Trong 1 Workbook, có nhiều sheet 1 -> 100. Em muốn tìm TẤT CẢ CÁC CELL tại sheet 1 (tìm cùng 1 lúc, do 1 sheet có rất nhiều data), đang nằm trong các cell có dạng Formular nào ở sheet khác
Do em muốn xoá bớt dữ liệu tại sheet 1 để nhẹ file mà ko ảnh hưởng đến các sheet khác
em sử dụng Trace Dependents & Trace Precedents trong tab Formulas thì chỉ xem được mỗi lần 1 cell thôi
Trân trọng cám ơn !

Góp ý cho bạn:
1/ Trong 1 Workbook có đến hàng trăm sheet là cách làm không hiệu quả, nếu không sử dụng VBA mà làm thủ công thì việc di chuyển đến sheet cần thì mất quá nhiều thời gian để tìm.
2/ Theo tôi thì nên nghĩ đến hướng khác hoặc bố trí dữ liệu cho thích hợp và lưu vào 1 sheet theo dõi (tôi xem sheet này là 1 cái kho chứa dụng cụ), muốn làm công việc gì đó thì vào kho lấy công cụ thích hợp ra sử dụng.
3/ Tốt nhất là bạn nên nêu cụ thể, rõ ràng là 100 sheet đó có là 1 mẫu thống nhất hay không, nếu nó cùng là 1 mẫu thì nên đưa File lên và nhờ các thành viên góp ý và đưa ra giải pháp thích hợp hơn.

 

[Maika8008]

Hi anh VetMini, em chưa hiểu ý của anh, hic. Mục đích của em là nếu sheet cần test không có cell nào liên quan đến các cell có chứa Formular nằm trong các sheet khác, thì có thể an tâm xoá sheet đó đi cho nhẹ file. Cám ơn anh !

Giả sử tìm sheet1 thấy có tham chiếu đến sheet2 và không tham chiếu sheet3 => Bạn xóa sheet3, nhưng sheet2 lại có tham chiếu sheet3. Thế là lỗi cả chùm sheet1 sheet2

 

[huuthang_bd]

Hi Anh huuthang_bd, nhờ anh hướng dẫn giùm em code này ý nghĩa ntn. Em trỏ chuột vào sheet cần test & và mở code lên chạy, sau khi chạy, thấy tên sheet đó thay đổi thành "DatTenGiChaDuoc", ngoài ra không thấy có gì khác, không biết em có làm sai gì không, nhờ anh hướng dẫn giúp. Cảm ơn anh !

Tôi quên đổi lại tên cũ cho sheet. Với code ở bài #3, các ô được tham chiếu đến sheet khác sẽ tô màu đỏ.
Tôi sửa lại, trả về tên cũ của sheet và hiện thông báo có/ không có sheet khác tham chiếu đến sheet hiện tại.

Const TenGi = "DatTenGiChaDuoc"
Sub UsedCll()
Dim ThisSh As Worksheet, ShName As String, Sh As Worksheet, Cll As Range, CllAddress As String, Check As Boolean
Set ThisSh = ActiveSheet
ShName = ThisSh.Name
ThisSh.Name = TenGi
Application.ReferenceStyle = xlA1
For Each Sh In Worksheets
    If Not Sh Is ThisSh Then
        Set Cll = Sh.Cells.Find(What:=TenGi & "!", LookIn:=xlFormulas, LookAt:=xlPart, SearchFormat:=False)
        If Not Cll Is Nothing Then
            CllAddress = Cll.Address
            Do
                If Cll.HasFormula Then FindRef ThisSh, Cll.Formula, Check
                Set Cll = Sh.Cells.FindNext(Cll)
            Loop Until Cll.Address = CllAddress
        End If
    End If
Next
ThisSh.Name = ShName
MsgBox IIf(Check, "CO", "KHONG CO") & " sheet khac tham chieu den sheet " & ShName
End Sub
Private Sub FindRef(ByRef ThisSh As Worksheet, ByVal sFormula As String, ByRef Check As Boolean)
Dim Arr As Variant, sRef As String, i As Long, j As Long
Arr = Split(sFormula, TenGi & "!")
For i = 1 To UBound(Arr, 1)
    sRef = Arr(i) & " "
    For j = 2 To Len(sRef)
        If Mid(sRef, j) Like "[!A-Z0-9:$]" Then
            ThisSh.Range(Left(sRef, j - 1)).Interior.Color = vbRed
            Check = True
        End If
    Next
Next
End Sub

Giả sử tìm sheet1 thấy có tham chiếu đến sheet2 và không tham chiếu sheet3 => Bạn xóa sheet3, nhưng sheet2 lại có tham chiếu sheet3. Thế là lỗi cả chùm sheet1 sheet2

Nếu muốn xóa sheet3 sẽ kiểm tra các sheet còn lại có tham chiếu đến sheet3 hay không, không phải kiểm tra sheet1.
Với yêu cầu này tôi đoán thớt đang muốn chỉnh sửa 1 file có sẵn, xây dựng lại từ đầu 1 file có hàng trăm sheet không phải là điều đơn giản. Ngoài ra 1 file có nhiều sheet không hẳn là không hiệu quả, nếu có ngần ấy báo cáo khác nhau thì mỗi sheet 1 báo cáo là bình thường.

 

[bomberman211]

Nhờ Anh/Chị hỗ trợ giúp trường hợp của em:
Trong 1 Workbook, có nhiều sheet 1 -> 100. Em muốn tìm TẤT CẢ CÁC CELL tại sheet 1 (tìm cùng 1 lúc, do 1 sheet có rất nhiều data), đang nằm trong các cell có dạng Formular nào ở sheet khác
Do em muốn xoá bớt dữ liệu tại sheet 1 để nhẹ file mà ko ảnh hưởng đến các sheet khác
em sử dụng Trace Dependents & Trace Precedents trong tab Formulas thì chỉ xem được mỗi lần 1 cell thôi
Trân trọng cám ơn !

Hồi trước ngồi ngâm cứu cái file của sếp cũ cũng bị vướng cái này, trace dependents từng cell một thì đến tết. Lục lọi trên google thì tìm được đoạn code này, bạn dùng thử xem. Cần tra dependents của vùng nào thì chọn vùng đó (trường hợp của bạn là toàn bộ data trong sheet 1)

Sub TraceDependents()
Dim xRg As Range
Dim xCell As Range
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
For Each xCell In xRg
xCell.ShowDependents
Next
End Sub

 

[minhphung0111]

Tôi quên đổi lại tên cũ cho sheet. Với code ở bài #3, các ô được tham chiếu đến sheet khác sẽ tô màu đỏ.
Tôi sửa lại, trả về tên cũ của sheet và hiện thông báo có/ không có sheet khác tham chiếu đến sheet hiện tại.

Const TenGi = "DatTenGiChaDuoc"
Sub UsedCll()
Dim ThisSh As Worksheet, ShName As String, Sh As Worksheet, Cll As Range, CllAddress As String, Check As Boolean
Set ThisSh = ActiveSheet
ShName = ThisSh.Name
ThisSh.Name = TenGi
Application.ReferenceStyle = xlA1
For Each Sh In Worksheets
    If Not Sh Is ThisSh Then
        Set Cll = Sh.Cells.Find(What:=TenGi & "!", LookIn:=xlFormulas, LookAt:=xlPart, SearchFormat:=False)
        If Not Cll Is Nothing Then
            CllAddress = Cll.Address
            Do
                If Cll.HasFormula Then FindRef ThisSh, Cll.Formula, Check
                Set Cll = Sh.Cells.FindNext(Cll)
            Loop Until Cll.Address = CllAddress
        End If
    End If
Next
ThisSh.Name = ShName
MsgBox IIf(Check, "CO", "KHONG CO") & " sheet khac tham chieu den sheet " & ShName
End Sub
Private Sub FindRef(ByRef ThisSh As Worksheet, ByVal sFormula As String, ByRef Check As Boolean)
Dim Arr As Variant, sRef As String, i As Long, j As Long
Arr = Split(sFormula, TenGi & "!")
For i = 1 To UBound(Arr, 1)
    sRef = Arr(i) & " "
    For j = 2 To Len(sRef)
        If Mid(sRef, j) Like "[!A-Z0-9:$]" Then
            ThisSh.Range(Left(sRef, j - 1)).Interior.Color = vbRed
            Check = True
        End If
    Next
Next
End Sub

Nếu muốn xóa sheet3 sẽ kiểm tra các sheet còn lại có tham chiếu đến sheet3 hay không, không phải kiểm tra sheet1.
Với yêu cầu này tôi đoán thớt đang muốn chỉnh sửa 1 file có sẵn, xây dựng lại từ đầu 1 file có hàng trăm sheet không phải là điều đơn giản. Ngoài ra 1 file có nhiều sheet không hẳn là không hiệu quả, nếu có ngần ấy báo cáo khác nhau thì mỗi sheet 1 báo cáo là bình thường.

Đúng rồi anh, em đang xây dựng lại file nhận từ người cũ. Em chạy thử code thì rất nhẹ và mượt. Nhưng có 1 lỗi trong code: vẫn tô đỏ các ô có lin

Tôi quên đổi lại tên cũ cho sheet. Với code ở bài #3, các ô được tham chiếu đến sheet khác sẽ tô màu đỏ.
Tôi sửa lại, trả về tên cũ của sheet và hiện thông báo có/ không có sheet khác tham chiếu đến sheet hiện tại.

Const TenGi = "DatTenGiChaDuoc"
Sub UsedCll()
Dim ThisSh As Worksheet, ShName As String, Sh As Worksheet, Cll As Range, CllAddress As String, Check As Boolean
Set ThisSh = ActiveSheet
ShName = ThisSh.Name
ThisSh.Name = TenGi
Application.ReferenceStyle = xlA1
For Each Sh In Worksheets
    If Not Sh Is ThisSh Then
        Set Cll = Sh.Cells.Find(What:=TenGi & "!", LookIn:=xlFormulas, LookAt:=xlPart, SearchFormat:=False)
        If Not Cll Is Nothing Then
            CllAddress = Cll.Address
            Do
                If Cll.HasFormula Then FindRef ThisSh, Cll.Formula, Check
                Set Cll = Sh.Cells.FindNext(Cll)
            Loop Until Cll.Address = CllAddress
        End If
    End If
Next
ThisSh.Name = ShName
MsgBox IIf(Check, "CO", "KHONG CO") & " sheet khac tham chieu den sheet " & ShName
End Sub
Private Sub FindRef(ByRef ThisSh As Worksheet, ByVal sFormula As String, ByRef Check As Boolean)
Dim Arr As Variant, sRef As String, i As Long, j As Long
Arr = Split(sFormula, TenGi & "!")
For i = 1 To UBound(Arr, 1)
    sRef = Arr(i) & " "
    For j = 2 To Len(sRef)
        If Mid(sRef, j) Like "[!A-Z0-9:$]" Then
            ThisSh.Range(Left(sRef, j - 1)).Interior.Color = vbRed
            Check = True
        End If
    Next
Next
End Sub

Nếu muốn xóa sheet3 sẽ kiểm tra các sheet còn lại có tham chiếu đến sheet3 hay không, không phải kiểm tra sheet1.
Với yêu cầu này tôi đoán thớt đang muốn chỉnh sửa 1 file có sẵn, xây dựng lại từ đầu 1 file có hàng trăm sheet không phải là điều đơn giản. Ngoài ra 1 file có nhiều sheet không hẳn là không hiệu quả, nếu có ngần ấy báo cáo khác nhau thì mỗi sheet 1 báo cáo là bình thường.

Em dùng code của anh và đã xử lý được, em nhận file từ người cũ nên đang build lại. Cám ơn anh nhiều ! ^^