add new row under multiples if conditions

[Ted0012]

Hi everyone, I am facing a problem in Excel. First, I need to use VBA because it is impossible to add a row in excel without using VBA (of what I know...).

The problem I am facing is that I have a data structure with thousands of lines in a tree type within the same column:

BASIC COLUMN
C1
C1R1
C1R1R1
C1R1R1R1
C1R1R1R2
C1R2
C1R2R1
C1R2R1R1
C2
C2R1
C2R1R1
C2R1R1R1
C2R2
C3
C4

What I would like is that creating a tree network graphic with x and y coordinates by creating key pairs of nodes in type "from-to". (e.g., C1-C1R1; C1R1-C1R1R1; C1R1R1-C1R1R1R1; C1R1R1-C1R1R1R2; and so on...). Each line will contain other information x,y coordinates, attributes, names etc. The thing is that I need to add a new line in order to take into account the "from" node under certain conditions that have to be defined. I have created the basic algorithm in excel in order to understand the logic in doing it with VBA. Here is the data structure I would like to look like afterwards. In bold, you have the new line added (the "from" node) copied.

WANTED RESULT:
C1
C1R1
C1R1
C1R1R1
C1R1R1
C1R1R1R1
C1R1R1
C1R1R1R2
C1
C1R2
C1R2
C1R2R1
C1R2R1
C1R2R1R1
C2
C2R1
C2R1
C2R1R1
C2R1R1
C2R1R1R1
C2
C2R2
C3
C3
C4
C4


Here is the algorithm developed in excel but that can't be used because we can't add rows in excel. But it gives le logic:


Algorithm:

=IF(B17="";
"";
IF(AND(LEN(B17) > LEN(B16);LEN(B17) < LEN(B18));
“add new line and copy B17”;
IF(AND(LEN(B17) > LEN(B16);LEN(B17) > LEN(B18));
"";
IF(AND(LEN(B17) < LEN(B16);LEN(B17) = LEN(B18));
B17;
IF(AND(LEN(B17) < LEN(B16);LEN(B17) < LEN(B18));
B17;
IF(LEN(B17) = LEN(B18);
B16;
IF(AND(LEN(B17)=2;LEN(B18)=2);
B17;
IF(AND(LEN(B17)=2;LEN(B18)="");
“add new line and copy B17”;
IF(LEFT(B17;4) <> LEFT(B18;4);
"Come back to the parent node";
""
)))))))))

Any thoughts? It would save me so much time. Thanks in advance.
Cheers.

 

[sergioMabres]

Hi Ted
I thought your problem could be solved, because inserting rows is very simple indeed, but I wanted to test your formula first, and only works some times, so sometimes it works fine, sometimes does not predict a needed insertion and other times predicts an unnecessary insertion, see pic:

Errors in red
Cheers
Sergio

 

[Ted0012]

Hi Sergio
Many thanks for your quick reply! Much appreciated! Indeed there are some mistakes in the formula that I have to fix . However, do you know how to do it with VBA with inserting rows? If yes, would you mind to share the VBA code?

Thanks! Cheers

 

[sergioMabres]

Hi Ted
Sure that is not hard to do, if you change your formula to show "add valuex" where you want a new row below and the new row with value = "valuex" this macro will work.
You put your original column in column A your formula showing nothing or "add valuex" in column B
Then you select the range A2:B16 and call the macro
The macro will insert a row below the "add" and copy the value besides "add" to coll A in the newly added row
Here is the sample macro

Sub checksandinsert()
'
' Inserts a row below where col 2 of selected range contains "add"
' Puts the string after "add" from col 2 in col 1 of the inserted row
'
   Dim r As Range
   Dim i As Integer
   Dim newnode As String
   Set r = Selection
   ' loops trhu the range
   For i = 1 To r.Rows.Count
      If Mid(r.Cells(i, 2), 1, 3) = "add" Then
         newnode = Mid(r.Cells(i, 2), 5, 50)
         r.Cells(i + 1, 2).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
         i = i + 1
         r.Cells(i, 1).Formula = newnode
      End If
   Next i
End Sub

Also if you change the IF condition "Mid(r.Cells(i, 2), 1, 3) = "add"" to your formula you do not even need an auxiliary column, but I cannot do that because your formula is not working as requiered

Cheers
Sergio

 

[sergioMabres]

Hi Ted
There was an error in the code I posted when the "add" was in the last row of the selected range, here is the corrected code

Sub checksandinsert()
'
' Inserts a row below where col 2 of selected range contains "add"
' Puts the string after "add" from col 2 in col 1 of the inserted row
'
   Dim r As Range
   Dim i, rc As Integer
   Dim newnode As String
   Set r = Selection
   ' loops trhu the range
   rc = r.Rows.Count
   i = 0
   Do While i <= rc
      i = i + 1
      If Mid(r.Cells(i, 2), 1, 3) = "add" Then
         newnode = Mid(r.Cells(i, 2), 5, 50)
         r.Cells(i + 1, 2).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
         i = i + 1
         rc = rc + 1
         r.Resize(rc, 2).Select
         Set r = Selection
         r.Cells(i, 1).Formula = newnode
      End If
   Loop
End Sub

Cheers
Sergio