In the situation that you had to parse worksheet with much formula, what would you do? You would trace formula to first cell which has no reference. In this article, I’d like to describe to find the first cells with wading through spaghetti formula. <\/p>\n

In order to demonstrate that set A is equal to set B, you should demonstrate that the union is equal to the intersection. <\/p>\n

When you compare DirectPrecendents property and Precendents property, which refer to direct reference range and all reference range, respectively, if the former is equal to the later, the range is the first cell. It’s assumed that no range refers to other worksheets and they have no cyclic references. <\/p>\n

You could constitute tree structure from first cell to last cell or from the last to the first, respectively. It’s a common technique to configure deployment folders or components. <\/p>\n

\r\nOption Explicit\r\nSub FirstPrecedents()\r\n    Dim Sh1 As Worksheet\r\n    Dim Sh2 As Worksheet\r\n    Dim i   As Long\r\n    Dim tmp As Range\r\n    Set Sh1 = ActiveSheet\r\n    Set Sh2 = Worksheets.Add\r\n    Sh2.Name = \"TraceFormula\"\r\n    i = 1\r\n    For Each tmp In Sh1.UsedRange\r\n        On Error Resume Next\r\n        If Left(tmp.Formula, 1) = \"=\" Then\r\n            If CheckEqualRange(tmp.DirectPrecedents, tmp.Precedents) Then\r\n                With Sh2\r\n                    .Cells(i, 1) = tmp.Address\r\n                    .Cells(i, 2) = \"'\" & tmp.Formula\r\n                    .Cells(i, 3) = tmp.DirectPrecedents.Address\r\n                    .Cells(i, 4) = tmp.Precedents.Address\r\n                    .Cells(i, 5) = tmp.DirectPrecedents.Cells.Count\r\n                    .Cells(i, 6) = tmp.Precedents.Cells.Count\r\n                    .Cells(i, 7) = CheckEqualRange(tmp.DirectPrecedents, tmp.Precedents)\r\n                End With\r\n                tmp.DirectPrecedents.Interior.Color = RGB(242, 220, 219)\r\n                i = i + 1\r\n            End If\r\n        End If\r\n        On Error GoTo 0\r\n    Next tmp\r\nEnd Sub\r\n\r\nFunction CheckEqualRange(ByRef Rng1 As Range, ByRef Rng2 As Range) As Boolean\r\n    Dim UnionRange      As Range\r\n    Dim IntersectRange  As Range\r\n    Dim tmp             As Range\r\n    CheckEqualRange = False\r\n    Set UnionRange = Application.Union(Rng1, Rng2)\r\n    Set IntersectRange = Application.Intersect(Rng1, Rng2)\r\n    If UnionRange.Cells.Count = IntersectRange.Cells.Count Then\r\n        CheckEqualRange = True\r\n    End If\r\nEnd Function\r\n<\/pre>\nI’d like to present other code with DirectDependents property and DirectPrecedents property of range object. It’s the first cell that the range has DirectDependents property but has no DirectPrecedents property. <\/p>\n
\r\nOption Explicit\r\nSub FirstPrecedents2()\r\n    Dim Sh1 As Worksheet\r\n    Dim Sh2 As Worksheet\r\n    Dim i   As Long\r\n    Dim tmp As Range\r\n    Set Sh1 = ActiveSheet\r\n    Set Sh2 = Worksheets.Add\r\n    Sh2.Name = \"Root\"\r\n    i = 1\r\n    For Each tmp In Sh1.UsedRange\r\n        On Error Resume Next\r\n        If Left(tmp.Formula, 1) = \"=\" Then\r\n            If tmp.DirectPrecedents Is Nothing And _\r\n               Not tmp.DirectDependents Is Nothing Then\r\n                Sh2.Cells(i, 1) = tmp.Address\r\n                i = i + 1\r\n            End If\r\n        End If\r\n        On Error GoTo 0\r\n    Next tmp\r\nEnd Sub\r\n<\/pre>\nAt last, I’d like to present the code to get the last cells that have opposite Boolean value of conditional expression. <\/p>\n
\r\nOption Explicit\r\nSub LastDependents()\r\n    Dim Sh1 As Worksheet\r\n    Dim Sh2 As Worksheet\r\n    Dim i   As Long\r\n    Dim tmp As Range\r\n    Set Sh1 = ActiveSheet\r\n    Set Sh2 = Worksheets.Add\r\n    Sh2.Name = \"Leaf\"\r\n    i = 1\r\n    For Each tmp In Sh1.UsedRange\r\n        On Error Resume Next\r\n        If Left(tmp.Formula, 1) = \"=\" Then\r\n            If tmp.DirectDependents Is Nothing And _\r\n                Not tmp.DirectPrecedents Is Nothing Then\r\n                tmp.Interior.Color = RGB(220, 230, 241)\r\n                Sh2.Cells(i, 1) = tmp.Address\r\n                i = i + 1\r\n            End If\r\n        End If\r\n        On Error GoTo 0\r\n    Next tmp\r\nEnd Sub\r\n<\/pre>\n<\/div>","protected":false},"excerpt":{"rendered":"In the situation that you had to parse worksheet with much formula, what would you do? You would trace formula … “How to get first cell which is referred to formula and last cell which refers to formula in Excel worksheet?” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":6026,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-3457","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3457"}],"version-history":[{"count":10,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3457\/revisions"}],"predecessor-version":[{"id":3467,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3457\/revisions\/3467"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/media\/6026"}],"wp:attachment":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3457"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}