add new row under multiples if conditions

Ted0012

New Member
Joined
May 27, 2016
Messages
2
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:
Ted.jpg


Errors in red
Cheers
Sergio
 
Upvote 0
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
 
Upvote 0
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
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 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
 
Upvote 0
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
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top