'Trace Dependencies for user defined range and list Source Cell/Dependency Cell in separate sheet - follow up
I have a follow up question to the solution that was provided to an earlier problem I had posted.
The solution worked perfectly but I realized it would be helpful if the headers of the respective column where the dependencies were found, would also be listed.
I added a code line to have the user determine what row the headers are but (I am having difficulties) I need help with the code to program a loop that inserts the headers above every dependency (as many as are found)
Sub Dependency_Check()
Dim rng As Range
Dim r As Range
Dim cell As Range
Dim n As Long, i As Long, j As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim h As Long 'header row
Dim c As Long 'column number
Application.ScreenUpdating = False
'Use InputBox to prompt user for range.
'Test for cancel and a single-cell selection.
Set rng = Application.InputBox( _
Title:="Please select a range", _
Prompt:="Select range", _
Type:=8)
h = InputBox("Row # where Headers are located.", "Row Input")
On Error GoTo 0
'Test for cancel.
If rng Is Nothing Then Exit Sub
'Test for single-cell selection.
'Remove comment character if single-cell selection is okay.
If rng.Rows.Count > 1 Then
MsgBox "You’ve selected more than 1 row. Please select contiguous cells per row only."
End If
'rng.Select to confirm selection
'MsgBox rng.Address
'check if sheet exists, if so, delete and create new one
For Each ws In Worksheets
If ws.Name = "IPT-CPT Conversion Check" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
Sheets.Add
ActiveSheet.Name = "IPT-CPT Conversion Check"
'add first cell of range in B2, second in C2 etc until end of range
'then add first dependent of first range cell in B4, second in C4 etc
Dim ra As Range, r1 As Range, r2 As Range
Worksheets("IPT-CPT Conversion Check").Select
Cells(2, 1).Value = "IPT"
Cells(4, 1).Value = "CPT"
j = 2
For Each ra In rng.Areas
For Each r1 In ra
Cells(2, j) = r1.Address
c = r1.Column
'below code is what i was trying
'Cells(1, j) = r1.Cells(2, j).Value
i = 4
For Each r2 In r1.Dependents
Cells(i, j) = r2.Address
'below code is what i was trying
'Cells(3, j) = r2.Cells(3, h)
i = i + 1
Next r2
j = j + 1
Next r1
Next ra
End Sub
Solution Sheet - Draft
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|